Hi all,
I've been stuck on this problem and it's killing me now!
I basically have a code which creates drop down list objects. The code below essentially creates 2 rows and "Selection.Offset(0, 8).Value" columns (which is a cell 8 columns to the left that contains an integer value) of drop-down boxes depending on how many we need. The group of drop-down box objects created are offset 2 rows right and 1 row down as seen by the offset function specified in "MYRANGE".
The issue here is that I'm assigning this macro to a master drop-down box. When the user selects a value from the drop-down box, the macro runs to produce a dimension of drop-down boxes as specified above.
The only issue is this: for my macro to run properly, I would have to first select the cell the master drop-down box sits in before I select a value from the master drop-down box. I want to be able to run the macro without having to select the underlying cell. When i click on the object, I see that the appropriate cell formula is in the formula bar. I.E. If a master drop-down box is sitting in cell $C$35, the address bar actually shows value $C$35.
Please help
Many thanks
I've been stuck on this problem and it's killing me now!
I basically have a code which creates drop down list objects. The code below essentially creates 2 rows and "Selection.Offset(0, 8).Value" columns (which is a cell 8 columns to the left that contains an integer value) of drop-down boxes depending on how many we need. The group of drop-down box objects created are offset 2 rows right and 1 row down as seen by the offset function specified in "MYRANGE".
The issue here is that I'm assigning this macro to a master drop-down box. When the user selects a value from the drop-down box, the macro runs to produce a dimension of drop-down boxes as specified above.
The only issue is this: for my macro to run properly, I would have to first select the cell the master drop-down box sits in before I select a value from the master drop-down box. I want to be able to run the macro without having to select the underlying cell. When i click on the object, I see that the appropriate cell formula is in the formula bar. I.E. If a master drop-down box is sitting in cell $C$35, the address bar actually shows value $C$35.
Please help
Many thanks
Code:
[/COLOR]Sub initiating() On Error GoTo 0
Dim c As Range, myRange As Range
Set myRange = Range(Selection.Offset(1, 2), Selection.Offset(Selection.Offset(0, 8).Value, 3))
If Selection.Offset(0, 8).Value >= 1 Then
For Each c In myRange.Cells
ActiveSheet.DropDowns.Add(c.Left, c.Top, 63, 15.75).Select
With Selection
.ListFillRange = "type"
.LinkedCell = c.Address
.DropDownLines = 8
.Display3DShading = False
.Locked = True
.LockAspectRatio = msoFalse
.Height = 13.5
.Width = 65.25
End With
Next
myRange.Select
Else
End If
End Sub[COLOR=#333333]
