Problem with UserForm1.Show
 

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.

Problem with UserForm1.Show
 

"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"
wrote in message
...
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.

Problem with UserForm1.Show
 

And it's just that easy! Thanks Jim!!

On Feb 14, 12:27*pm, "Jim Cone" wrote:
"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"
wrote in ...



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.- Hide quoted text -

- Show quoted text -

Problem with UserForm1.Show
 

Steve,
You are welcome... appreciate getting the feedback.
'---
Jim Cone



"Steve"
wrote in message
...
And it's just that easy! Thanks Jim!!

On Feb 14, 12:27 pm, "Jim Cone"
wrote:
"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"
wrote in messagenews:
...
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.- Hide quoted text -

- Show quoted text -

Your Date Picker
 

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 will pop 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 up in some of them. Would you be so kind to help me on this one, please?

Kind regards
Petra