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.?

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.?

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.?