Difficulty in naming the range

[xrull]

I need some expert help.
I'm a battering VBA coder (beginner).
How would I make the Sheet Name dynamic for this formula? I need to
type the name of the sheet (Mask or any other name) in a cell, say A1,
and the VBA code below would pick it up and make the necessary
changes:

Private Sub ComboBox1_GotFocus()
Dim listRange As String

listRange = "Masks!A2:" & _
Worksheets("Masks").Range("A2").End(xlDown).Address
ComboBox1.ListFillRange = listRange
End Sub

Thanks

 

[Ajay]

I need some expert help.
I'm a battering VBA coder (beginner).
How would I make the Sheet Name dynamic for this formula? I need to
type the name of the sheet (Mask or any other name) in a cell, say A1,
and the VBA code below would pick it up and make the necessary
changes:

Private Sub ComboBox1_GotFocus()
Dim listRange As String

listRange = "Masks!A2:" & _
Worksheets("Masks").Range("A2").End(xlDown).Address
ComboBox1.ListFillRange = listRange
End Sub

Thanks

Can you explain more what do you exactly want to do

 

[Socko]

Please refer to the following proc... Insert this procedure in a
worksheet module where the combobox is placed. Remember to change the
name of the combobox from "mycombo" to the name of your combobox. Use
a1 as the cell to change the name of a sheet... you may not call this
procedure , as this is an event proc, and will execute as you change
the value in the cell A!



Private Sub Worksheet_Change(ByVal Target As Range)
'CELLS(1,1) IS THE REFERENCE TO THE CELL WHERE SHEET NAME
EXISTS
If UCase(Target.Address) = "$A$1" Then
With mycombo
.Clear
.ListFillRange = Cells(1, 1) & "!$a:$a"
End With
End If
End Sub


I hope this helps.

Selva V Pasupathy
For more on Excel, VBA, & other resources
Please visit http://socko.wordpress.com/

 

[Dave Peterson]

I'd use this:

Private Sub ComboBox1_GotFocus()
Dim listRange As Range 'changed!

with worksheets("masks")
set listrange = .range("a2", .range("a2").end(xldown))
'or coming from the bottom towards the top
set listrange = .range("a2",.cells(.rows.count,"A").end(xlup))
end with

me.combobox1.listfillrange = listrange.address(external:=true)

End Sub

 

[xrull]

I need some expert help.
I'm a battering VBA coder (beginner).
How would I make the Sheet Name dynamic for this formula? I need to
type the name of the sheet (Mask or any other name) in a cell, say A1,
and the VBA code below would pick it up and make the necessary
changes:

Private Sub Worksheet_Change(ByVal Target As Range)

'CELLS(1,1) IS THE REFERENCE TO THE CELL WHERE SHEET NAME
EXISTS
If UCase(Target.Address) = "$A$1" Then
With mycombo
.Clear
.ListFillRange = Cells(1, 1) & "!$a:$a"
End With
End If
End Sub

 

[xrull]

My file has 4 worksheets, and Sheets named Masks, Gloves, Hats, and
Check has a list in Column A:A, Sheet Check is the sheet with the
combo box. I'd like to use the cell A1 to type the name of the sheet
(whether it be sheet Masks, Gloves, or Hat so the name combo box will
show that list, instead of using 3 combo boxes for each sheet, I'd
like to use one.