Problem with UserForm1.Show

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

Hi All. I have the following bits of code:

Sub Main()
UserForm1.Show
End Sub

Private Sub UserForm_Initialize()
Set mRng = Sheets("Picklist").Range(Cells(2, "a"), Cells(2,
"a").End(xlDown))
For Each c In mRng
ComboBox1.AddItem c
Next c
End Sub

I would like my Sheet "Picklist" to be hidden. When it is hidden, my
code errors out on the line UserForm1.Show. When I unhide the sheet
"Picklist" and execute the code, it works perfectly.

Any ideas on how I can execute the code while the sheet "Picklist" is
hidden??

Thanks so much.
 
"Cells" refers to the active sheet unless you tell Excel differently.
A hidden workbook is not active and has no active sheets.
Change your code so that "Cells" has a parent called out...
'---
Set mRng = _
Sheets("Picklist").Range(Sheets("Picklist").Cells(2, "a"), _
Sheets("Picklist").Cells(2, "a").End(xlDown))
'---
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(Extras for Excel: Date picker, Clean Data, Classic Menu ...)




"Steve" <[email protected]>
wrote in message
news:2460f6ff-659e-43f5-9e2c-ba093e177db7@y10g2000vbn.googlegroups.com...
 
And it's just that easy! Thanks Jim!!

"Cells" refers to the active sheet unless you tell Excel differently.
A hidden workbook is not active and has no active sheets.
Change your code so that "Cells" has a parent called out...
'---
Set mRng = _
Sheets("Picklist").Range(Sheets("Picklist").Cells(2, "a"), _
Sheets("Picklist").Cells(2, "a").End(xlDown))
'---
Jim Cone
Portland, Oregon USAhttp://www.mediafire.com/PrimitiveSoftware
(Extras for Excel:  Date picker, Clean Data, Classic Menu ...)

"Steve" <[email protected]>
wrote in message






- Show quoted text -
Click to expand...
 
Steve,
You are welcome... appreciate getting the feedback.
'---
Jim Cone



"Steve" <[email protected]>
wrote in message
And it's just that easy! Thanks Jim!!

On Feb 14, 12:27 pm, "Jim Cone"
 
Dear Jim,

Sorry for popping up in the wrong group but this a way to be able to contact you.

Thanks so much for sharing your Date Picker with the wider community. I have downloaded it and also copied the VBA coding so that the date picker willpop up automatically. As I don/t use VBA often, I am a bit stuck at adapting it to my needs. I am creating a Travel Request Form in Design View with various fields. In some of them I would like to have the date picker pop upin some of them. Would you be so kind to help me on this one, please?

Kind regards
Petra
 
Back
Top