Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform Initialize & Command Button, Excel 2000 & 2003
Hello,
I have a userform with a combo box and a commandbutton. The combobox uses the .additem, currently it has 11 items but will be added to. My UserForm_Initialize is below: Private Sub UserForm_Initialize() Dim varData(10) As Variant varData(0) = "" varData(1) = "Search" varData(2) = "Find" varData(3) = "GoTo" varData(4) = "Compare Data" varData(5) = "Delete Characters" varData(6) = "Number Cells 1-25" varData(7) = "Number Sells Value 001" varData(8) = "Find Blank Cell" varData(9) = "Fill Selection Copy" varData(10) = "Fill Selection Series" With ComboBox1 .AddItem varData(0) .AddItem varData(1) .AddItem varData(2) .AddItem varData(3) .AddItem varData(4) .AddItem varData(5) .AddItem varData(6) .AddItem varData(7) .AddItem varData(8) .AddItem varData(9) .AddItem varData(10) End With End Sub My commandbutton is below: Private Sub CommandButton3_Click() cb = ComboBox1 If cb = "" Then 'Do Nothing ElseIf cb = "Search" Then Call UserFormFIND ElseIf cb = "Find" Then Call FindDialogBoxOpen ElseIf cb = "GoTo" Then Call GoToDialogBoxOpen ElseIf cb = "Compare Data" Then Call CompareData ElseIf cb = "Delete Charcters" Then Call DeleteCharcters ElseIf cb = "Number Cells 1-25" Then Call NumberCells ElseIf cb = "Number Cels Value 001" Then Call NumberCellsValue ElseIf cb = "Find Blank Cells" Then Call FindBlankCell ElseIf cb = "Fill Selection Copy" Then Call Selection.FillDown ElseIf cb = "Fill Selection Series" Then Call FillSelection_Series End If End Sub All of my elseif has the same name as my UserForm_Initialize. How can I use the UserForm_Initialize varData array in my command button so that I do not have to type the list twice? Thank you for your help, jfcby |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform Initialize & Command Button, Excel 2000 & 2003
Put the declaration -- Dim varData(10) As Variant -- before any
procedures. That makes it available to all procedures in the module. Hth, Merjet |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform Initialize & Command Button, Excel 2000 & 2003
First, you'll want to watch your spelling--and case is important in stuff like
this, too. "Delete Characters" would never match: "Delete Charcters" But instead of checking the names, maybe just using the index: Option Explicit Dim varMacs As Variant Private Sub UserForm_Initialize() Dim varData As Variant varData = Array("", "Search", "Find", "GoTo", _ "Compare Data", _ "Delete Characters", "Number Cells 1-25", _ "Number Cells Value 001", "Find Blank Cell", _ "Fill Selection Copy", "Fill Selection Series") varMacs = Array("", "UserFormFind", "FindDialogBoxOpen", _ "GoToDialogBoxOpen", _ "CompareData", _ "DeleteCharacters", "NumberCells", _ "NumberCellsValue", "FindBlankcell", _ "SelectionFillDown", "FillSelectionSeries") If UBound(varData) < UBound(varMacs) Then MsgBox "Design error!" Exit Sub End If With ComboBox1 .List = varData End With End Sub Private Sub CommandButton1_Click() If Me.ComboBox1.ListIndex < 1 Then Beep Exit Sub Else 'Application.Run "'" & ThisWorkbook.Name & "'!" & varMacs(.ListIndex) MsgBox "'" & ThisWorkbook.Name & "'!" & varMacs(Me.ComboBox1.ListIndex) End If End Sub You'll want to make sure that the macro names actually match the names you used in that varMacs assignment--no typos <vbg! And I used msgbox instead of actually creating those routines. jfcby wrote: Hello, I have a userform with a combo box and a commandbutton. The combobox uses the .additem, currently it has 11 items but will be added to. My UserForm_Initialize is below: Private Sub UserForm_Initialize() Dim varData(10) As Variant varData(0) = "" varData(1) = "Search" varData(2) = "Find" varData(3) = "GoTo" varData(4) = "Compare Data" varData(5) = "Delete Characters" varData(6) = "Number Cells 1-25" varData(7) = "Number Sells Value 001" varData(8) = "Find Blank Cell" varData(9) = "Fill Selection Copy" varData(10) = "Fill Selection Series" With ComboBox1 .AddItem varData(0) .AddItem varData(1) .AddItem varData(2) .AddItem varData(3) .AddItem varData(4) .AddItem varData(5) .AddItem varData(6) .AddItem varData(7) .AddItem varData(8) .AddItem varData(9) .AddItem varData(10) End With End Sub My commandbutton is below: Private Sub CommandButton3_Click() cb = ComboBox1 If cb = "" Then 'Do Nothing ElseIf cb = "Search" Then Call UserFormFIND ElseIf cb = "Find" Then Call FindDialogBoxOpen ElseIf cb = "GoTo" Then Call GoToDialogBoxOpen ElseIf cb = "Compare Data" Then Call CompareData ElseIf cb = "Delete Charcters" Then Call DeleteCharcters ElseIf cb = "Number Cells 1-25" Then Call NumberCells ElseIf cb = "Number Cels Value 001" Then Call NumberCellsValue ElseIf cb = "Find Blank Cells" Then Call FindBlankCell ElseIf cb = "Fill Selection Copy" Then Call Selection.FillDown ElseIf cb = "Fill Selection Series" Then Call FillSelection_Series End If End Sub All of my elseif has the same name as my UserForm_Initialize. How can I use the UserForm_Initialize varData array in my command button so that I do not have to type the list twice? Thank you for your help, jfcby -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform Initialize & Command Button, Excel 2000 & 2003
I would check the spelling between you array values and your if statements.
If you are going to call different procedures you could use the listindex property. select Case combobox1.listindex Case -1 ' do nothing Case 0 UserFormFIND Case 1 FindDialogBoxOpen Case 2 GoToDialogBoxOpen . . . End Select -- Regards, Tom Ogilvy "jfcby" wrote in message oups.com... Hello, I have a userform with a combo box and a commandbutton. The combobox uses the .additem, currently it has 11 items but will be added to. My UserForm_Initialize is below: Private Sub UserForm_Initialize() Dim varData(10) As Variant varData(0) = "" varData(1) = "Search" varData(2) = "Find" varData(3) = "GoTo" varData(4) = "Compare Data" varData(5) = "Delete Characters" varData(6) = "Number Cells 1-25" varData(7) = "Number Sells Value 001" varData(8) = "Find Blank Cell" varData(9) = "Fill Selection Copy" varData(10) = "Fill Selection Series" With ComboBox1 .AddItem varData(0) .AddItem varData(1) .AddItem varData(2) .AddItem varData(3) .AddItem varData(4) .AddItem varData(5) .AddItem varData(6) .AddItem varData(7) .AddItem varData(8) .AddItem varData(9) .AddItem varData(10) End With End Sub My commandbutton is below: Private Sub CommandButton3_Click() cb = ComboBox1 If cb = "" Then 'Do Nothing ElseIf cb = "Search" Then Call UserFormFIND ElseIf cb = "Find" Then Call FindDialogBoxOpen ElseIf cb = "GoTo" Then Call GoToDialogBoxOpen ElseIf cb = "Compare Data" Then Call CompareData ElseIf cb = "Delete Charcters" Then Call DeleteCharcters ElseIf cb = "Number Cells 1-25" Then Call NumberCells ElseIf cb = "Number Cels Value 001" Then Call NumberCellsValue ElseIf cb = "Find Blank Cells" Then Call FindBlankCell ElseIf cb = "Fill Selection Copy" Then Call Selection.FillDown ElseIf cb = "Fill Selection Series" Then Call FillSelection_Series End If End Sub All of my elseif has the same name as my UserForm_Initialize. How can I use the UserForm_Initialize varData array in my command button so that I do not have to type the list twice? Thank you for your help, jfcby |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform Initialize & Command Button, Excel 2000 & 2003
On Feb 18, 6:38 pm, "Tom Ogilvy" wrote:
I would check the spelling between you array values and your if statements. If you are going to call different procedures you could use the listindex property. select Case combobox1.listindex Case -1 ' do nothing Case 0 UserFormFIND Case 1 FindDialogBoxOpen Case 2 GoToDialogBoxOpen . . . End Select -- Regards, Tom Ogilvy "jfcby" wrote in message oups.com... Hello, I have a userform with a combo box and a commandbutton. The combobox uses the .additem, currently it has 11 items but will be added to. My UserForm_Initialize is below: Private Sub UserForm_Initialize() Dim varData(10) As Variant varData(0) = "" varData(1) = "Search" varData(2) = "Find" varData(3) = "GoTo" varData(4) = "Compare Data" varData(5) = "Delete Characters" varData(6) = "Number Cells 1-25" varData(7) = "Number Sells Value 001" varData(8) = "Find Blank Cell" varData(9) = "Fill Selection Copy" varData(10) = "Fill Selection Series" With ComboBox1 .AddItem varData(0) .AddItem varData(1) .AddItem varData(2) .AddItem varData(3) .AddItem varData(4) .AddItem varData(5) .AddItem varData(6) .AddItem varData(7) .AddItem varData(8) .AddItem varData(9) .AddItem varData(10) End With End Sub My commandbutton is below: Private Sub CommandButton3_Click() cb = ComboBox1 If cb = "" Then 'Do Nothing ElseIf cb = "Search" Then Call UserFormFIND ElseIf cb = "Find" Then Call FindDialogBoxOpen ElseIf cb = "GoTo" Then Call GoToDialogBoxOpen ElseIf cb = "Compare Data" Then Call CompareData ElseIf cb = "Delete Charcters" Then Call DeleteCharcters ElseIf cb = "Number Cells 1-25" Then Call NumberCells ElseIf cb = "Number Cels Value 001" Then Call NumberCellsValue ElseIf cb = "Find Blank Cells" Then Call FindBlankCell ElseIf cb = "Fill Selection Copy" Then Call Selection.FillDown ElseIf cb = "Fill Selection Series" Then Call FillSelection_Series End If End Sub All of my elseif has the same name as my UserForm_Initialize. How can I use the UserForm_Initialize varData array in my command button so that I do not have to type the list twice? Thank you for your help, jfcby Hello All responders, Thank you for your help. Problem was solved with Tom's select case response. jfcby |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
UserForm List Box, Excel 2000 & 2003 | Excel Programming | |||
UserForm Question, Excel 2000 & 2003 | Excel Programming | |||
Sin Button run macro Excel 2000 - 2003 | Excel Programming | |||
How do I setup a print command button in excel 2003 | New Users to Excel | |||
Userform disappears when you try to initialize from a command button | Excel Programming |