Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Initializing User Forms
You don't need to go through two arrays to do this:
Private Sub fmPickStore_Initialize() cbStoreList.ColumnCount = 2 cbStoreList.List = _ ThisWorkbook.Worksheets("Chart Data").Range("b3:c22").Value End Sub If you only want to load 1 column Private Sub fmPickStore_Initialize() cbStoreList.List = _ ThisWorkbook.Worksheets("Chart Data").Range("B3:B22").Value End Sub -- Regards, Tom Ogilvy "Marcotte A" wrote in message ... I am having trouble initializing my user forms. I have a drop down list that I want populated from a range on my spreadsheet. When that spreadsheet is not active, the combobox doesn't get initialized. This is the initialize code. I thought the 'ThisWorkbook' part would take care of it, but it doesn't seem to. What am I doing wrong? Private Sub fmPickStore_Initialize() Dim varStoreList As Variant Dim arrStoreList(20, 2) As Variant Dim i As Integer varStoreList = ThisWorkbook.Worksheets("Chart Data").Range("b3:c22") For i = 1 To 20 arrStoreList(i, 1) = varStoreList(i) Next i cbStoreList.List = arrStoreList End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Initializing User Forms
"Tom Ogilvy" wrote:
You don't need to go through two arrays to do this: Private Sub fmPickStore_Initialize() cbStoreList.ColumnCount = 2 cbStoreList.List = _ ThisWorkbook.Worksheets("Chart Data").Range("b3:c22").Value End Sub Thanks Tom. I knew there was a more efficient way to do this. However, I am still not seeing the list when another workbook is active. When the workbook containing the userform is active it works fine, but if I go to another workbook, then into the VBE and run my main() sub (which loads the userform) I get an empty dropdown list. Here is the code: 'In Module2 Public CurrentStore As Integer Public InputType As String Public CurrentDate As Date Sub main() CurrentDate = Date MainMenu.Show End Sub 'Partial code for MainMenu form Private Sub cmProduction_Click() InputType = "ProdSpoil" Me.Hide fmpickstore.Show End Sub 'Partial code for fmPickStore Private Sub fmPickStore_Initialize() cbStoreList.ColumnCount = 2 cbStoreList.List = _ ThisWorkbook.Worksheets("Chart Data").Range("b3:c22").Value End Sub Any ideas? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Initializing User Forms
The initialize event always has the name
Private Sub Userform_Initialize() so your sub is never run change to Private Sub Userform_Initialize() cbStoreList.ColumnCount = 2 cbStoreList.List = _ ThisWorkbook.Worksheets("Chart Data").Range("b3:c22").Value End Sub It is always best to use the dropdowns at the top of the module to enter events - leads to fewer mistakes such as this. -- Regards, Tom Ogilvy "Marcotte A" wrote in message ... "Tom Ogilvy" wrote: You don't need to go through two arrays to do this: Private Sub fmPickStore_Initialize() cbStoreList.ColumnCount = 2 cbStoreList.List = _ ThisWorkbook.Worksheets("Chart Data").Range("b3:c22").Value End Sub Thanks Tom. I knew there was a more efficient way to do this. However, I am still not seeing the list when another workbook is active. When the workbook containing the userform is active it works fine, but if I go to another workbook, then into the VBE and run my main() sub (which loads the userform) I get an empty dropdown list. Here is the code: 'In Module2 Public CurrentStore As Integer Public InputType As String Public CurrentDate As Date Sub main() CurrentDate = Date MainMenu.Show End Sub 'Partial code for MainMenu form Private Sub cmProduction_Click() InputType = "ProdSpoil" Me.Hide fmpickstore.Show End Sub 'Partial code for fmPickStore Private Sub fmPickStore_Initialize() cbStoreList.ColumnCount = 2 cbStoreList.List = _ ThisWorkbook.Worksheets("Chart Data").Range("b3:c22").Value End Sub Any ideas? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Initializing User Forms
"Tom Ogilvy" wrote:
The initialize event always has the name Private Sub Userform_Initialize() so your sub is never run change to Private Sub Userform_Initialize() cbStoreList.ColumnCount = 2 cbStoreList.List = _ ThisWorkbook.Worksheets("Chart Data").Range("b3:c22").Value End Sub It is always best to use the dropdowns at the top of the module to enter events - leads to fewer mistakes such as this. -- Regards, Tom Ogilvy I changed the name of the initialize sub as suggested and now get Runtime Error #70 'Permission Denied'. I switched it back to fmpickstore_initialize and it works fine. I added 'ThisWorkbook.Activate' to the initialize event for my main menu form, so its working all the time now. It's odd though, because each other userform initialize event is Sub UserForm_Initialize (), but when I change Sub fmPickStore_Initialize () to Sub UserForm_Initialize () I get that error. It makes no sense. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
user forms | Excel Discussion (Misc queries) | |||
User Forms | Excel Discussion (Misc queries) | |||
user forms | Excel Discussion (Misc queries) | |||
User forms | Excel Programming | |||
User forms in VBA | Excel Programming |