Problems with Combo Box
Hello all,
here is the problem that I'm experiencing. I have the an ComboBox1 inserted from control toolbar, all I want is to be able to change the sheets by clicking in the combobox like sheet1, sheet2, and sheet3. Only three sheets will do for now. But I have problem populating the combobox with sheet names lets call them sheet1..2 etc. How do I go about. It is fairly simple but how is it done. ?I am using excel 2000 on XP Pro. I have named the combobox to Sheet1 in the properties by right clicking on it, now what.? |
Problems with Combo Box
I'm no expert on this, but I'm pretty sure you're going to have to have your
sheet names in a central location in your workbook. I would suggest that you use sheet1 to store your sheet names. If you have a lot of sheets that may change, this could be done with a simple macro. This macro will gather a lists of worksheets in the current workbook and store those names in column Z beginning at Z1 and working down. You can change this as necessary. Sub WorksheetNameList() For i = 1 To Worksheets.Count Sheets("Sheet1").Range("Z" & i).Value = Sheets(i).Name Next End Sub The only way I can think of doing what you want, is to create a combo box on each worksheet of your workbook. In the properties of each combo box, set the ListFillRange and LinkedCell to: ListFillRange: Sheet1!Z1:Z5 LinkedCell: Sheet1!AA1 If you create the first Combo Box with this setup, you can then just copy it from one page to the next. Once that's done, right-click on a combo box and go to View Code. Use the following code. Private Sub ComboBox1_Change() Sheets(Sheets("Sheet1").Range("AA1").Value).Activa te End Sub You should be able to do what you want now. There may be other ways to do this, but I hope this gets you started. Paul "aalam" wrote in message ... Hello all, here is the problem that I'm experiencing. I have the an ComboBox1 inserted from control toolbar, all I want is to be able to change the sheets by clicking in the combobox like sheet1, sheet2, and sheet3. Only three sheets will do for now. But I have problem populating the combobox with sheet names lets call them sheet1..2 etc. How do I go about. It is fairly simple but how is it done. ?I am using excel 2000 on XP Pro. I have named the combobox to Sheet1 in the properties by right clicking on it, now what.? |
Problems with Combo Box
Enter Design Mode Make The Name of the combobox cboSheets Open Vb Editor Tools Macro Visual Basic Editor Double-Click on This Workbook icon in the Project Properties. On the code sheet the drop down reads (General). Change this to" Workbook" A sub is created called workbook_open Workbook_Open() Dim i as integer for i = 1 to sheets.count cboSheets.additem sheets(i).name next cbosheets.listindex=0 end sub Double-Click on Sheet1 properties icon You should see Sub cboSheets_Change() Sub cboSheets_Change() sheets(cboSheets.text).select or sheets(cboSheets.list(cboSheets.listindex).select end sub "aalam" wrote: Hello all, here is the problem that I'm experiencing. I have the an ComboBox1 inserted from control toolbar, all I want is to be able to change the sheets by clicking in the combobox like sheet1, sheet2, and sheet3. Only three sheets will do for now. But I have problem populating the combobox with sheet names lets call them sheet1..2 etc. How do I go about. It is fairly simple but how is it done. ?I am using excel 2000 on XP Pro. I have named the combobox to Sheet1 in the properties by right clicking on it, now what.? |
All times are GMT +1. The time now is 09:32 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com