Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loading Sheet Names into Combo Box
I have four worksheets. The first sheet ("Inputs") has a combo bo
which loads the sheets names of all the sheets in the workbook. Th remaining three sheets are called "Case 1", "Case 2", and "Case 3". The problem is that the code below loads the sheet names properly but: 1) I do not want the input sheet included in the list 2) after the click event, the chosen item from the list does not sta loaded in the combo box What is wrong with this? Thanks Private Sub ComboBox1_Click() Dim Sh As Worksheet With ComboBox1 .Clear For Each Sh In ActiveWorkbook.Sheets .AddItem Sh.Name Next End With End Su -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loading Sheet Names into Combo Box
Private Sub ComboBox1_Click()
Dim Sh As Worksheet With ComboBox1 Clear For Each Sh In ActiveWorkbook.Sheets if sh.Name < "Inputs" then AddItem Sh.Name End if Next End With End Sub Not sure what you mean by doesn't stay loaded. If you mean it doesn't appear in the "textbox". What click event? What other event code do you have? -- Regards, Tom Ogilvy "ExcelMonkey " wrote in message ... I have four worksheets. The first sheet ("Inputs") has a combo box which loads the sheets names of all the sheets in the workbook. The remaining three sheets are called "Case 1", "Case 2", and "Case 3". The problem is that the code below loads the sheet names properly but: 1) I do not want the input sheet included in the list 2) after the click event, the chosen item from the list does not stay loaded in the combo box What is wrong with this? Thanks Private Sub ComboBox1_Click() Dim Sh As Worksheet With ComboBox1 Clear For Each Sh In ActiveWorkbook.Sheets AddItem Sh.Name Next End With End Sub --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loading Sheet Names into Combo Box
So Tom, when I use this code, the combo box pulls the sheets into th
list as I click onto the combo box button. Afer the click i completed, the chosen item from the list does not stay show up in th box. That is, it loads it into the list, allow me to pick it, but doe display the chosen item int eh box after the click. Is click the right event? Or should it be a Change event? 'This loads the ComboBox list Private Sub ComboBox1_Click() Dim Sh As Worksheet With ComboBox1 .Clear For Each Sh In ActiveWorkbook.Sheets If Sh.Name < "Inputs" Then .AddItem Sh.Name End If Next End With End Su -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loading Sheet Names into Combo Box
I look at your code and saw CommandButton1_click rather than
Combobox1_click - my inattention. The reason it doesn't stay loaded as you say, it because you rebuild the list on the click event. When the list is rebuilt, the selection is removed. 'This loads the ComboBox list Private Sub ComboBox1_Click() Dim Sh As Worksheet Dim sVal as String With ComboBox1 sVal = .Text Clear For Each Sh In ActiveWorkbook.Sheets If Sh.Name < "Inputs" Then AddItem Sh.Name End If Next .Value = sVal End With End Sub Should do it. Click event would be proper. I assume you are rebuilding the list because there might be sheets added. -- Regards, Tom Ogilvy "ExcelMonkey " wrote in message ... So Tom, when I use this code, the combo box pulls the sheets into the list as I click onto the combo box button. Afer the click is completed, the chosen item from the list does not stay show up in the box. That is, it loads it into the list, allow me to pick it, but does display the chosen item int eh box after the click. Is click the right event? Or should it be a Change event? 'This loads the ComboBox list Private Sub ComboBox1_Click() Dim Sh As Worksheet With ComboBox1 Clear For Each Sh In ActiveWorkbook.Sheets If Sh.Name < "Inputs" Then AddItem Sh.Name End If Next End With End Sub --- Message posted from http://www.ExcelForum.com/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loading Sheet Names into Combo Box
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loading Sheet Names into Combo Box
So that works fine Tom. Now here is my next question. Suppose I no
want the result of the combo box to trigger another routine tha selects that sheet that is chosen. That is I use the routine below t load sheet names into a combo box. I now want this to trigger anothe routine that selects the sheet. I am not sure where to put this routine. I know that it has to go i the sheet module. Do I pass the sheet names into an array and the pass this array to another routine? And lastly, I do not want this to run continuously. That is I want t be able to pick a sheet name with the combo box, have it go to tha sheet ONLY after the name has been chosen in the box. I guess I hav to use the right "event" to make sure that the routine does not ru continuously. Would this be the Change event? How would you do this? Thanks 'This loads the ComboBox list Private Sub ComboBox1_Click() Dim Sh As Worksheet Dim sVal As String With ComboBox1 sVal = .Text .Clear For Each Sh In ActiveWorkbook.Sheets If Sh.Name < "Inputs" Then .AddItem Sh.Name End If Next .Value = sVal End With End Su -- Message posted from http://www.ExcelForum.com |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loading Sheet Names into Combo Box
'This loads the ComboBox list
Private Sub ComboBox1_Click() Dim Sh As Worksheet Dim sVal as String With ComboBox1 sVal = .Text Clear For Each Sh In ActiveWorkbook.Sheets If Sh.Name < "Inputs" Then AddItem Sh.Name End If Next .Value = sVal Worksheets(.Value).Activate End With End Sub The click event is appropriate "ExcelMonkey " wrote in message ... So that works fine Tom. Now here is my next question. Suppose I now want the result of the combo box to trigger another routine that selects that sheet that is chosen. That is I use the routine below to load sheet names into a combo box. I now want this to trigger another routine that selects the sheet. I am not sure where to put this routine. I know that it has to go in the sheet module. Do I pass the sheet names into an array and then pass this array to another routine? And lastly, I do not want this to run continuously. That is I want to be able to pick a sheet name with the combo box, have it go to that sheet ONLY after the name has been chosen in the box. I guess I have to use the right "event" to make sure that the routine does not run continuously. Would this be the Change event? How would you do this? Thanks 'This loads the ComboBox list Private Sub ComboBox1_Click() Dim Sh As Worksheet Dim sVal As String With ComboBox1 sVal = .Text Clear For Each Sh In ActiveWorkbook.Sheets If Sh.Name < "Inputs" Then AddItem Sh.Name End If Next Value = sVal End With End Sub --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sheet Referencing - autofilling sheet names | Excel Worksheet Functions | |||
Cell names = sheet names | Excel Worksheet Functions | |||
Loading a Combo Box with the months of the year | Excel Discussion (Misc queries) | |||
Excel sheet not loading | Excel Discussion (Misc queries) | |||
VBA for opening file/populating combo box with sheet names | Excel Programming |