how to achieve this

[tkraju via OfficeKB.com]

In my user form,I have a combo box which has w/sheet names and two option
buttons 1.males,2.females. In each sheet of my w/book ,range b2:b4 = names of
males and range c2:c4 =names of females.I have 3 text boxes and cmd button
'populate'.If I select w/sheet name from combobox and select an option say 2.
females,and press populate button ,the relevant sheet's females names be
populated in 3 text boxes. I have created a user form but unable add code.
How to achieve this?.Any help is sincerely appreciated.

 

[Mike Fogleman]

Sub CommandButton1_Click() 'Populate
Dim ws As Worksheet
With UserForm1
Set ws = Worksheets(.ComboBox1.Value)
If .OptionButton1.Value = True Then 'males
.TextBox1.Value = ws.Range("B2")
.TextBox2.Value = ws.Range("B3")
.TextBox3.Value = ws.Range("B4")
Else 'females
.TextBox1.Value = ws.Range("C2")
.TextBox2.Value = ws.Range("C3")
.TextBox3.Value = ws.Range("C4")
End If
End With
End Sub

This is untested so give it a shot.
Mike F

 

[tkraju via OfficeKB.com]

Thanks,Mike,Its working great.How to enable optionbutton1 as default(true) ,
most of the times males option button use is morethan 90%.

Sub CommandButton1_Click() 'Populate
Dim ws As Worksheet
With UserForm1
Set ws = Worksheets(.ComboBox1.Value)
If .OptionButton1.Value = True Then 'males
.TextBox1.Value = ws.Range("B2")
.TextBox2.Value = ws.Range("B3")
.TextBox3.Value = ws.Range("B4")
Else 'females
.TextBox1.Value = ws.Range("C2")
.TextBox2.Value = ws.Range("C3")
.TextBox3.Value = ws.Range("C4")
End If
End With
End Sub

This is untested so give it a shot.
Mike F

In my user form,I have a combo box which has w/sheet names and two option
buttons 1.males,2.females. In each sheet of my w/book ,range b2:b4 = names

[quoted text clipped - 7 lines]

code.
How to achieve this?.Any help is sincerely appreciated.

 

[Jim May]

Mike:
The OP's Combobox1...(question).
How did/would you populate a combobox with
The sheet names (and of course make it dynamic)?
Thanks,
Jim

 

[Mike Fogleman]

For your OptionButton and Jim May's dynamic sheet name list, both can be
done in the UserForm Initialize code:

Private Sub UserForm_Initialize()
Dim ws As Worksheet

For Each ws In Worksheets
Me.ComboBox1.AddItem ws.Name
Next ws

Me.OptionButton1.Value = True
End Sub

Before you ask, the use of Me refers to the object that this code module
belongs to. This is Private code that resides in UserForm1, so Me referes to
UserForm1. If the code belonged to UserForm2 or Sheet1,etc., then Me would
refer to those objects. Kind of a short cut way to write the object's name

Mike F

Thanks,Mike,Its working great.How to enable optionbutton1 as default(true)
,
most of the times males option button use is morethan 90%.

Sub CommandButton1_Click() 'Populate
Dim ws As Worksheet
With UserForm1
Set ws = Worksheets(.ComboBox1.Value)
If .OptionButton1.Value = True Then 'males
.TextBox1.Value = ws.Range("B2")
.TextBox2.Value = ws.Range("B3")
.TextBox3.Value = ws.Range("B4")
Else 'females
.TextBox1.Value = ws.Range("C2")
.TextBox2.Value = ws.Range("C3")
.TextBox3.Value = ws.Range("C4")
End If
End With
End Sub

This is untested so give it a shot.
Mike F

In my user form,I have a combo box which has w/sheet names and two
option
buttons 1.males,2.females. In each sheet of my w/book ,range b2:b4 =
names

[quoted text clipped - 7 lines]

code.
How to achieve this?.Any help is sincerely appreciated.

 

[tkraju via OfficeKB.com]

Thanks Mike.Thank you so much.

For your OptionButton and Jim May's dynamic sheet name list, both can be
done in the UserForm Initialize code:

Private Sub UserForm_Initialize()
Dim ws As Worksheet

For Each ws In Worksheets
Me.ComboBox1.AddItem ws.Name
Next ws

Me.OptionButton1.Value = True
End Sub

Before you ask, the use of Me refers to the object that this code module
belongs to. This is Private code that resides in UserForm1, so Me referes to
UserForm1. If the code belonged to UserForm2 or Sheet1,etc., then Me would
refer to those objects. Kind of a short cut way to write the object's name

Mike F

Thanks,Mike,Its working great.How to enable optionbutton1 as default(true)
,

[quoted text clipped - 25 lines]