Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to achieve this
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. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200608/1 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to achieve this
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" <u16627@uwe wrote in message news:64341184d6db6@uwe... 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. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200608/1 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to achieve this
Thanks,Mike,Its working great.How to enable optionbutton1 as default(true) ,
most of the times males option button use is morethan 90%. Mike Fogleman wrote: 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. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200608/1 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to achieve this
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" wrote in message : 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" <u16627@uwe wrote in message news:64341184d6db6@uwe... 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. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200608/1 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to achieve this
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 "tkraju via OfficeKB.com" <u16627@uwe wrote in message news:6436fc09c0b18@uwe... Thanks,Mike,Its working great.How to enable optionbutton1 as default(true) , most of the times males option button use is morethan 90%. Mike Fogleman wrote: 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. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200608/1 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to achieve this
Thanks Mike.Thank you so much.
Mike Fogleman wrote: 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] code. How to achieve this?.Any help is sincerely appreciated. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200608/1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I create a cell formula to achieve this? | Excel Discussion (Misc queries) | |||
Increase amount to achieve target | Excel Worksheet Functions | |||
need help to achieve this calculation | Excel Discussion (Misc queries) | |||
Whether unknown does VBA achieve? | Excel Programming | |||
Is there a better method to achieve this? | Excel Programming |