Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use 1 userform for many sheets, Rather than 1 userform per sheet: How?
The below is the code that i am using to display and update values in cells:
X3 & X6 on a sheet. I have the rows and columns hiden from view, but the user can see the current and modify if need by by using the userform. However rather than creating a userform for each person, how can i use the 1 userform to look at the SAME cells (X3 & X6) DEPENDING on what sheet the userform was run from???? I though the simple: ACTIVESHEET.SELECT would do it but it seems to only look to the same sheet. Private Sub CommandButton1_Click() activessheet.select If Me.TextBox2.Value < "" Then Range("X3").Value = Me.TextBox2.Value End If If Me.TextBox4.Value < "" Then Range("X6").Value = Me.TextBox4.Value End If UserForm2.Hide MsgBox "The amounts have been Renewed.", , "title...." End Sub Private Sub CommandButton2_Click() UserForm2.Hide End Sub Private Sub Frame1_Click() End Sub Private Sub TextBox1_Change() With Me.TextBox1 End With End Sub Private Sub TextBox2_Change() End Sub Private Sub TextBox3_Change() End Sub Private Sub TextBox4_Change() End Sub Private Sub UserForm_Initialize() ActiveSheet.Select With Me.TextBox1 .ControlSource = "X3" End With With Me.TextBox3 .ControlSource = "X6" End With End Sub Regards Corey.... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use 1 userform for many sheets, Rather than 1 userform per sheet: How?
The unqualified range would assume the ActiveSheet.
Put a command button on a WS and add this code. select different worksheets and see that the range is updated, whichever sheet is active. <WS code Private Sub CommandButton1_Click() UserForm1.Show vbModeless End Sub </WS code <userform code Private Sub UserForm_Click() Range("A1").Value = "Value Added" End Sub </userform code NickHK "Corey" wrote in message ... The below is the code that i am using to display and update values in cells: X3 & X6 on a sheet. I have the rows and columns hiden from view, but the user can see the current and modify if need by by using the userform. However rather than creating a userform for each person, how can i use the 1 userform to look at the SAME cells (X3 & X6) DEPENDING on what sheet the userform was run from???? I though the simple: ACTIVESHEET.SELECT would do it but it seems to only look to the same sheet. Private Sub CommandButton1_Click() activessheet.select If Me.TextBox2.Value < "" Then Range("X3").Value = Me.TextBox2.Value End If If Me.TextBox4.Value < "" Then Range("X6").Value = Me.TextBox4.Value End If UserForm2.Hide MsgBox "The amounts have been Renewed.", , "title...." End Sub Private Sub CommandButton2_Click() UserForm2.Hide End Sub Private Sub Frame1_Click() End Sub Private Sub TextBox1_Change() With Me.TextBox1 End With End Sub Private Sub TextBox2_Change() End Sub Private Sub TextBox3_Change() End Sub Private Sub TextBox4_Change() End Sub Private Sub UserForm_Initialize() ActiveSheet.Select With Me.TextBox1 .ControlSource = "X3" End With With Me.TextBox3 .ControlSource = "X6" End With End Sub Regards Corey.... |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use 1 userform for many sheets, Rather than 1 userform per sheet: How?
Sorry Nick i do not understand what you mean ???
If i enter values intot he userform2 the values do not correspond to the sheet i run the userform from they always refer to the values on the same sheet. Corey.... "NickHK" wrote in message ... The unqualified range would assume the ActiveSheet. Put a command button on a WS and add this code. select different worksheets and see that the range is updated, whichever sheet is active. <WS code Private Sub CommandButton1_Click() UserForm1.Show vbModeless End Sub </WS code <userform code Private Sub UserForm_Click() Range("A1").Value = "Value Added" End Sub </userform code NickHK "Corey" wrote in message ... The below is the code that i am using to display and update values in cells: X3 & X6 on a sheet. I have the rows and columns hiden from view, but the user can see the current and modify if need by by using the userform. However rather than creating a userform for each person, how can i use the 1 userform to look at the SAME cells (X3 & X6) DEPENDING on what sheet the userform was run from???? I though the simple: ACTIVESHEET.SELECT would do it but it seems to only look to the same sheet. Private Sub CommandButton1_Click() activessheet.select If Me.TextBox2.Value < "" Then Range("X3").Value = Me.TextBox2.Value End If If Me.TextBox4.Value < "" Then Range("X6").Value = Me.TextBox4.Value End If UserForm2.Hide MsgBox "The amounts have been Renewed.", , "title...." End Sub Private Sub CommandButton2_Click() UserForm2.Hide End Sub Private Sub Frame1_Click() End Sub Private Sub TextBox1_Change() With Me.TextBox1 End With End Sub Private Sub TextBox2_Change() End Sub Private Sub TextBox3_Change() End Sub Private Sub TextBox4_Change() End Sub Private Sub UserForm_Initialize() ActiveSheet.Select With Me.TextBox1 .ControlSource = "X3" End With With Me.TextBox3 .ControlSource = "X6" End With End Sub Regards Corey.... |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use 1 userform for many sheets, Rather than 1 userform per sheet: How?
As you are hiding (rather than unloading) the userform, the initialisation
event only gets fired once. So the .ControlSource does not change. So either unload or reset the controlsource to the desired worksheet. ..ControlSource="sheet2!a3" Is that what you mean ? NickHK "Corey" wrote in message ... Sorry Nick i do not understand what you mean ??? If i enter values intot he userform2 the values do not correspond to the sheet i run the userform from they always refer to the values on the same sheet. Corey.... "NickHK" wrote in message ... The unqualified range would assume the ActiveSheet. Put a command button on a WS and add this code. select different worksheets and see that the range is updated, whichever sheet is active. <WS code Private Sub CommandButton1_Click() UserForm1.Show vbModeless End Sub </WS code <userform code Private Sub UserForm_Click() Range("A1").Value = "Value Added" End Sub </userform code NickHK "Corey" wrote in message ... The below is the code that i am using to display and update values in cells: X3 & X6 on a sheet. I have the rows and columns hiden from view, but the user can see the current and modify if need by by using the userform. However rather than creating a userform for each person, how can i use the 1 userform to look at the SAME cells (X3 & X6) DEPENDING on what sheet the userform was run from???? I though the simple: ACTIVESHEET.SELECT would do it but it seems to only look to the same sheet. Private Sub CommandButton1_Click() activessheet.select If Me.TextBox2.Value < "" Then Range("X3").Value = Me.TextBox2.Value End If If Me.TextBox4.Value < "" Then Range("X6").Value = Me.TextBox4.Value End If UserForm2.Hide MsgBox "The amounts have been Renewed.", , "title...." End Sub Private Sub CommandButton2_Click() UserForm2.Hide End Sub Private Sub Frame1_Click() End Sub Private Sub TextBox1_Change() With Me.TextBox1 End With End Sub Private Sub TextBox2_Change() End Sub Private Sub TextBox3_Change() End Sub Private Sub TextBox4_Change() End Sub Private Sub UserForm_Initialize() ActiveSheet.Select With Me.TextBox1 .ControlSource = "X3" End With With Me.TextBox3 .ControlSource = "X6" End With End Sub Regards Corey.... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
userform that add data in all w/sheets | Excel Discussion (Misc queries) | |||
Userform to enter values and shown in same userform in list | Excel Programming | |||
Looping procedure calls userform; how to exit loop (via userform button)? | Excel Programming | |||
Activating userform and filling it with data form row where userform is activate | Excel Programming | |||
Access from add_in userform to main template userform.... | Excel Programming |