Excel - Repositioning combobox in cells (dynamically) 1

[tb]

Hi everyone,

I am quite new to VBA ... I've been programming mainly in VB for quite some time.

My problem is the following ...
I need to display a combo box in Column D when and if the user moves to that spesific cell. (not be displayed the whole time)

I've manage to determine when I am on Column D and I've managed to add a combo box to my spreadsheet
Found 2 ways:
** ActiveSheet.DropDowns.Add(0, 76.5, 96, 15.75).Select
** ActiveSheet.OLEObjects.Add "Forms.ComboBox.1",
Left:=10, Top:=10, Height:=20, Width:=100

What I am trying to do now is to resize and reposition the combo box to a certain cell as the user enters information in the sheet and navigate to the specified cell.

I can get the top, left ect of the cell, but the coordinates is waaaayyy out ...

MsgBox "TOP " & Target.Cells(lRow, 4).Top
MsgBox "LEFT " & Target.Cells(lRow, 4).Left
MsgBox "WIDTH " & Target.Cells(lRow, 4).Width
MsgBox "HEIGHT " & Target.Cells(lRow, 4).Height

What is the unit of measure of the above ?
Pixels/Twips/

Is there an easier way to determine where I should position the combo box?

Hope I've explained the situation well.
Any help, tips, hints would be much appreciated.

I was standing in the park, wondering why frisbees got bigger as they came closer... then it hit me!

 

[SkipVought]

Hi,

When you add a drawing object to a sheet, it is added relative to the ActiveCell.

Each drawing object has several useful properties for positioning

TopLeftCell and BottomRightCell - the CELL (range object) that is at the top left or bottom right of the drawing object.

So to position the drawing object the same size and location of the to left cell, for instance

Set ob1 = ActiveSheet.OLEObjects.Add "Forms.ComboBox.1", 
   Left:=10, Top:=10, Height:=20, Width:=100
With ob1
  .top = .topleftcell.top
  .left = .topleftcell.left
  .width = .topleftcell.width
  .height = .topleftcell.height
end with
with

hope this helps


Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[tb]

Thanks Skip, it works like a charm !

A few more questions if I may ...

I need to remove the object when I move off the field..

I have tried the following without success ...
** ActiveSheet.OLEObjects(1).Delete
** ActiveSheet.OLEObjects("ComboBox1").Delete

Then my last question..
I use a statis variable to keep track of the column I'm on...but as soon as the combo box is loaded the static variable loses its value - I don't understand why

Thanks again for your speedy reply!

I was standing in the park, wondering why frisbees got bigger as they came closer... then it hit me!

 

[SkipVought]

I'd suggest using the Control Toolbox Combobox (dorpdown). Add, either programatically or using the control toolbox, ONE control of this type ONCE for your application -- like an initialization.

NOW, set the Visible property True of False, depending on the circunstance.

Then you use the Worksheet_SelectionChange event to control the object. You could like this...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  With Target
    If .Row > 2 And .Column =4 Then 
      ComboBox1.Top = .Top
      ComboBox1.Left = .Left
      ComboBox1.Width = .Width
      ComboBox1.Height = .Height
      ComboBox1.Visible = True
    Else
      ComboBox1.Visible = False
    End If
  End With
End Sub
Private ComboBox1_Click()
  With ComboBox1
    .TopLeftCell.Value = .Value 
    .Visible = False            
  End With
End Sub

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[SkipVought]

BTW,

The answer to your question

ActiveSheet.DrawingObjects("ComboBox1").Delete

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[tb]

I added the combo box on the spreadsheet.
On the Workbook_WindowActivate event I clear the combo (4 just in case) then populate it.

The rest of it is almost as you suggested it.. minor modifications.

Thanks again Skip - much appreciated!!



I was standing in the park, wondering why frisbees got bigger as they came closer... then it hit me!