Range Naming Problems

[xrull]

Let me try this again.
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 ( I found it on the code here:
http://www.microsoft.com/office/com...m=1&query=Dynamic+combo+box&type=question&p=1)
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

My file has 4 worksheets, and Sheets named Masks, Gloves, Hats, and
Check. Sheets Mask, Glove, Hats each has a list in Column A:A.
Sheet Check has the combobox1.
I'd like to use the cell A1 on the sheet named Check to refer to the
sheet in the vba. What code can I use instead of hard wiring the word
"Mask", or "Gloves" or "Hats" in my code? I want to use one combo box
(Instead of 3 combo boxes) to call that list from the Sheets "Mask,
Gloves, and Hats from Column A:A.
Thanks for helping me,
Joni

 

[Dave Peterson]

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

with worksheets(me.range("A1").value)
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]

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

  with worksheets(me.range("A1").value)
    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

Thanks Dave Peterson,
The code worked like a charm. When I grow up, I want to be like you
and the other Excel experts.
Xrull.

 

[Ajay]

Thanks Dave Peterson,
The code worked like a charm. When I grow up, I want to be like you
and the other Excel experts.
Xrull.- Hide quoted text -

- Show quoted text -

I sent you one excel file. Please let me know, if you have received
that. That file would solve your problem.