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

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

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

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