Use 1 userform for many sheets, Rather than 1 userform per sheet: How?

[Corey]

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

 

[NickHK]

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]

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]

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]

thanks for the reply Nick.
I cannot get my head around what to do here.
I changed the code I had to be now this:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub CommandButton1_Click()
Unload Userform2 ' <= added
ActiveSheet.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
MsgBox "The amounts have been Renewed.", , "title...."
End Sub
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Some times the userform seems to show the active sheets data values, yet
other times i change i set of values from say sheet3 and then go to sheet10,
and the values there have also changed.

I am not sure where to put an Unload line,
OR
where/what to put in the controlSource??

I only have 1 userform, and if i place a sheet/cell range there wont it
ALWAYS look to that sheet?
Or can i place an activesheet code there?


I cannot get this right.

Corey....

 

[NickHK]

A userform is not tied to any specific worksheet.
Unless you specify otherwise, it will interact with the ActiveSheet.

Maybe you need to use sheet _Acivate event to update the text boxes if the
userform is showing.

And you will want the Unload Me at the end of that routine.

NickHK