I want to create a combo box with sheetnames of w/book.how?

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200603/1

One way is to load the combobox with the sheet names as the workbook opens,
the example fills the ComboBox1 on worksheet 1 e.g..


Private Sub Workbook_Open()
Dim ws As Integer
With Sheets(1).ComboBox1
For ws = 1 To Sheets.Count
.AddItem Sheets(ws).Name
Next
End With
End Sub

--
Cheers
Nigel



"tkraju via OfficeKB.com" <u16627@uwe wrote in message
news:5dc4602d21ce9@uwe...
I want to create a combo box with sheetnames of w/book.how?

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200603/1

thanks Nigel,I am sorry I put my question wrong,load a combo box with sheet
names in a userform,in w/sheet 1.

Nigel wrote:
One way is to load the combobox with the sheet names as the workbook opens,
the example fills the ComboBox1 on worksheet 1 e.g..

Private Sub Workbook_Open()
Dim ws As Integer
With Sheets(1).ComboBox1
For ws = 1 To Sheets.Count
.AddItem Sheets(ws).Name
Next
End With
End Sub

I want to create a combo box with sheetnames of w/book.how?

--
Message posted via http://www.officekb.com

On a userform add the code into the Userform initialize event e.g.

Private Sub UserForm_Initialize()
Dim ws As Integer
For ws = 1 To Sheets.Count
ComboBox1.AddItem Sheets(ws).Name
Next
End Sub

--
Cheers
Nigel



"tkraju via OfficeKB.com" <u16627@uwe wrote in message
news:5dc525ca3b37a@uwe...
thanks Nigel,I am sorry I put my question wrong,load a combo box with
sheet
names in a userform,in w/sheet 1.

Nigel wrote:
One way is to load the combobox with the sheet names as the workbook
opens,
the example fills the ComboBox1 on worksheet 1 e.g..

Private Sub Workbook_Open()
Dim ws As Integer
With Sheets(1).ComboBox1
For ws = 1 To Sheets.Count
.AddItem Sheets(ws).Name
Next
End With
End Sub

I want to create a combo box with sheetnames of w/book.how?

--
Message posted via http://www.officekb.com

thanks Nigel,Its working fine,but after saving this w/book with this code ,
while reopening this w/book ,I am unable to locate my userform in any of my
w/book sheets.It is not visible anywhere.

Nigel wrote:
On a userform add the code into the Userform initialize event e.g.

Private Sub UserForm_Initialize()
Dim ws As Integer
For ws = 1 To Sheets.Count
ComboBox1.AddItem Sheets(ws).Name
Next
End Sub

thanks Nigel,I am sorry I put my question wrong,load a combo box with
sheet
[quoted text clipped - 14 lines]

I want to create a combo box with sheetnames of w/book.how?

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200603/1

Modify your Workbook_Open Code as follows:

Private Sub Workbook_Open()
Dim ws As Integer
With Sheets(1).ComboBox1
For ws = 1 To Sheets.Count
.AddItem Sheets(ws).Name
Next
End With
UserFormName.show '<<< Change to actual UF Name
End Sub



"tkraju via OfficeKB.com" <u16627@uwe wrote in message
news:5dc7db7ec9a1d@uwe...
thanks Nigel,Its working fine,but after saving this w/book with this code
,
while reopening this w/book ,I am unable to locate my userform in any of
my
w/book sheets.It is not visible anywhere.

Nigel wrote:
On a userform add the code into the Userform initialize event e.g.

Private Sub UserForm_Initialize()
Dim ws As Integer
For ws = 1 To Sheets.Count
ComboBox1.AddItem Sheets(ws).Name
Next
End Sub

thanks Nigel,I am sorry I put my question wrong,load a combo box with
sheet
[quoted text clipped - 14 lines]

I want to create a combo box with sheetnames of w/book.how?

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200603/1

Did you save it in the Personal.xls hidden workbook. Take a look in the VB
editor and see if it is there, you can drag it across.

--
Cheers
Nigel



"tkraju via OfficeKB.com" <u16627@uwe wrote in message
news:5dc7db7ec9a1d@uwe...
thanks Nigel,Its working fine,but after saving this w/book with this code
,
while reopening this w/book ,I am unable to locate my userform in any of
my
w/book sheets.It is not visible anywhere.

Nigel wrote:
On a userform add the code into the Userform initialize event e.g.

Private Sub UserForm_Initialize()
Dim ws As Integer
For ws = 1 To Sheets.Count
ComboBox1.AddItem Sheets(ws).Name
Next
End Sub

thanks Nigel,I am sorry I put my question wrong,load a combo box with
sheet
[quoted text clipped - 14 lines]

I want to create a combo box with sheetnames of w/book.how?

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200603/1