Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel VB - show userform1 (at same time) minimized application win | Excel Discussion (Misc queries) | |||
how do i use UserForm1.Show vbModeless command? | Excel Programming | |||
UserForm1.show fails if another workbook is open | Excel Programming | |||
What's the difference between 'Set UserForm1=Nothing' and 'Unload UserForm1' ? | Excel Programming | |||
SetFocus to first textbox on userform upon Userform1.Show | Excel Programming |