Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Refresh combobox after inserting new sheet
Hi,
I'm really new to VBA programming, but I'm trying to learn from everyone here. :) I have an ActiveX ComboBox that is populated with the names of all the worksheets. All I want to do is refresh the list of worksheet names in the combobox whenever a worksheet is added or deleted from the workbook. Any ideas? Thanks, Brian --- Message posted from http://www.ExcelForum.com/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Refresh combobox after inserting new sheet
Brian,
Paste this code in the ThisWorkbook code module in the VB editor. It assumes a "ComboBox1" in "Sheet1." The code runs each time a new sheet is activated, which happens whenever you insert or delete a sheet: Private Sub Workbook_SheetActivate(ByVal Sh As Object) Dim ws As Worksheet With Worksheets("Sheet1").ComboBox1 .Clear For Each ws In ThisWorkbook.Worksheets .AddItem ws.Name Next ws End With End Sub hth, Doug "Duke17 " wrote in message ... Hi, I'm really new to VBA programming, but I'm trying to learn from everyone here. :) I have an ActiveX ComboBox that is populated with the names of all the worksheets. All I want to do is refresh the list of worksheet names in the combobox whenever a worksheet is added or deleted from the workbook. Any ideas? Thanks, Brian --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Refresh combobox after inserting new sheet
Hey Doug thanks a lot for your reply. I had to make a slight change t
get it to work though...I changed: With Worksheets("Sheet2").ComboBox1 TO With Sheet2.ComboBox1 (I take it they're the same?) I noticed that this code will take effect even if the user simpl clicks on another worksheet. I only need it to update if there's bee a new worksheet added, or a current one deleted. Do you or anyone els have any ideas -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Refresh combobox after inserting new sheet
"Duke17 " wrote in message ... With Sheet2.ComboBox1 (I take it they're the same?) Actually they're not. WorkSheets("Sheet2") or ("mysheet"), etc., refer to the names in the sheet tabs. Sheet2, without the quotes, is the codename, which appears in the VBE Project Explorer under Microsoft Excel Objects, for example -- Sheet2("mysheet"). I noticed that this code will take effect even if the user simply clicks on another worksheet. I only need it to update if there's been a new worksheet added, or a current one deleted. Do you or anyone else have any ideas? I don't have any other ideas. What is the function of the ComboBox? I understand that it runs every time you switch sheets. What problem is that causing? Doug Message posted from http://www.ExcelForum.com/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Refresh combobox after inserting new sheet
Well, data in the spreadsheet is loaded according to the name in th
combobox...so when I click on another spreadsheet, it refreshes th combobox, and sets it back to the first spreadsheet in the list. If it would only refresh when a new spreadsheet is inserted that woul be cool. Or after refreshing, would it be possible to have it se itself back to the previously selected name in the combobox, instead o resetting it to the first name in the list -- Message posted from http://www.ExcelForum.com |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Refresh combobox after inserting new sheet
Ok...I've accomplished getting the combobox1 to reset to the previou
selected value. No problem there. But as expected, it's a little slo because everytime you click on another sheet now, it has to reload th data. How would I allow this code to execute ONLY when a new sheet is adde or deleted? I think with this change, everything will be perfect -- Message posted from http://www.ExcelForum.com |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Refresh combobox after inserting new sheet
Put all of the following in the ThisWorkbook module. It uses the NewSheet
event, obvious enough. It also takes advantage of the fact that the SheetDeactivate event fires before sheet deletion and the SheetActivate fires after sheet deletion. It uses a public variable to check if there's a different number of sheets during the Deactivate and Activate events. If there is then, and only then, does it call the combobox refresh sub. It also keeps the combobox value between refreshes. Also, it runs the refresh when the workbook is first opened, so get rid of that if you don't want. The above statements about the order of Deactivate, delete, and Activate are just based on my experimentation, but I think it's true (XL 2k). Let me know how it works for you: Option Explicit Dim sheets_count As Long Private Sub Workbook_Open() Call refresh_cbox End Sub Private Sub Workbook_NewSheet(ByVal Sh As Object) Call refresh_cbox End Sub Private Sub Workbook_SheetActivate(ByVal Sh As Object) If sheets_count ThisWorkbook.Sheets.Count Then Call refresh_cbox End If End Sub Private Sub Workbook_SheetDeactivate(ByVal Sh As Object) sheets_count = ThisWorkbook.Sheets.Count End Sub Sub refresh_cbox() Dim ws As Worksheet Dim cbox_idx As Long With Worksheets("Sheet1").ComboBox1 'track cbox value before refresh cbox_idx = .ListIndex .Clear For Each ws In ThisWorkbook.Worksheets .AddItem ws.Name Next ws 'if new sheet inserted before old cbox value If ActiveSheet.Index <= cbox_idx + 1 Then cbox_idx = cbox_idx + 1 End If 'ensure valid listindex on workbook open and delete If cbox_idx + 1 <= .ListCount And cbox_idx = 0 Then .ListIndex = cbox_idx Else .ListIndex = 0 End If 'reset the value .Value = .List(.ListIndex) End With End Sub hth, Doug Glancy "Duke17 " wrote in message ... Ok...I've accomplished getting the combobox1 to reset to the previous selected value. No problem there. But as expected, it's a little slow because everytime you click on another sheet now, it has to reload the data. How would I allow this code to execute ONLY when a new sheet is added or deleted? I think with this change, everything will be perfect. --- Message posted from http://www.ExcelForum.com/ |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Refresh combobox after inserting new sheet
Well, I haven't been able to get it to work right away.
1. Whenever refresh_cbox is called I get an error stating that th subscript is out of range. I changed Sheet1 to Sheet2 so it looks lik this: With Worksheets("Sheet2").ComboBox1 2. Workbook_NewSheet is called when a new sheet is inserted. That i correct. It was my fault for not mentioning this, but most of the tim a new sheet will be inserted as a copy of an existing worksheet. :eek Whenever this happens, Workbook_NewSheet is not called. Is ther another event handler for this that I should use? Thanks for all of your help and patience Doug! Bria -- Message posted from http://www.ExcelForum.com |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Refresh combobox after inserting new sheet
Brian,
1. Whenever refresh_cbox is called I get an error stating that the subscript is out of range. I changed Sheet1 to Sheet2 so it looks like this: With Worksheets("Sheet2").ComboBox1 Provided the Worksheet is named "Sheet2" exactly, I don't know what the problem could be. 2. Workbook_NewSheet is called when a new sheet is inserted. That is correct. It was my fault for not mentioning this, but most of the time a new sheet will be inserted as a copy of an existing worksheet. Whenever this happens, Workbook_NewSheet is not called. Is there another event handler for this that I should use? Sorry, but I'm not able to figure out anything else. I still like the original code - it was simple at least. But if it's too slow, it's too slow. If I think of anything, I'll let you know. You could try posting again with what you've got. Doug Glancy |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Refresh combobox after inserting new sheet
Workbook_NewSheet() is something recognized as part of VBA? Or is i
completely custom? I need a function that triggers when a worksheet has been copied. I' just like to know if something like this already exists, or if I nee to create this myself -- Message posted from http://www.ExcelForum.com |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Refresh combobox after inserting new sheet
Brian,
It is a VBA-defined event. In the ThisWorkbook module, where you've been pasting the code I gave you, choose "Workbook" from the top-left dropdown in the code window. Then choose the top-right dropdown to see all the VBA workbook events. Unfortunately, there is no CopySheet event. For more info try "Workbook Events" in VBA help. hth, Doug "Duke17 " wrote in message ... Workbook_NewSheet() is something recognized as part of VBA? Or is it completely custom? I need a function that triggers when a worksheet has been copied. I'd just like to know if something like this already exists, or if I need to create this myself? --- Message posted from http://www.ExcelForum.com/ |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Refresh combobox after inserting new sheet
Hey Doug,
I found a solution that works! It turned out to be very easy. Instea of trying to complicate things by detecting when a new sheet was adde and how, I just decided to have the combobox refresh every time tha sheet with the combobox (call it sheet2) was opened. I figured if a sheet gets copied or inserted, the selected sheet wil change away from sheet2. When they activate sheet2, the combobox wil then have the new sheet included. Works like a charm. Thanks for all the help Doug -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
RowSource for Sheet ComboBox | Excel Worksheet Functions | |||
Sheet combobox help | Excel Worksheet Functions | |||
HELP PLEASE..Inserting combobox (ActiveX control) makes workbook c | Excel Discussion (Misc queries) | |||
Inserting a row in sheet A should Insert a row in sheet B, removing a row in Sheet A should remove the corresponding row in sheet B | Excel Programming | |||
Inserting a combobox | Excel Programming |