Userform/sheet selection again

[blackie42]

Hi,

I posted yesterday regarding using a userform to update a sheet then after the sheet updates (with commandbutton) opening another userform and checking which sheet is active - and dependent on that updating its paired sheet. There are the steps (but the paired sheet won't activate)

Open form1 from activesheet
input textbox details and hit cmd button to send to appropriate cells
close form 1 and open form 2
input textbox details and hit cmd button. first code in cmd button is
to check the name of active cell and dependent on result activate the
paired sheet (and then send the textbox details to the appropriate cells)
- Only it will not activate or select (or using with worksheet method as per Lucas last reply) the paired sheet. Details from userform 2 are entered in original activesheet.

[VBA]Private Sub CommandButton1_Click()
If ActiveSheet.Name = "CMFSPLIT" Then
Worksheets("CMFRUN").Activate
End If
If ActiveSheet.Name = "EUFSPLIT" Then
Worksheets("EUFRUN").Activate
End If
Range("C16").Value = TextBox1.Value
Range("E16").Value = TextBox4.Value
Range("C45").Value = TextBox2.Value
Range("E45").Value = TextBox5.Value
Range("C75").Value = TextBox3.Value
Range("E75").Value = TextBox6.Value
End Sub[/VBA]

Any ideas what I'm doing wrong

thanks
Jon

[georgiboy]

When you run the code do you have sheet "CMFSPLIT" or sheet "EUFSPLIT" activated, otherwise it will populate the sheet you are in with the textbox values.

[georgiboy]

Try something more like this...

[vba]Private Sub CommandButton1_Click()
Dim shName As String

On Error GoTo errHand

If ActiveSheet.Name = "CMFSPLIT" Then
shName = "CMFRUN"
ElseIf ActiveSheet.Name = "EUFSPLIT" Then
shName = "EUFRUN"
End If

With Sheets(shName)
.Range("C16").Value = TextBox1.Value
.Range("E16").Value = TextBox4.Value
.Range("C45").Value = TextBox2.Value
.Range("E45").Value = TextBox5.Value
.Range("C75").Value = TextBox3.Value
.Range("E75").Value = TextBox6.Value
End With

errHand:
End Sub[/vba]
Hope this helps

[blackie42]

Hi, GB

thanks for reply - I tried your code but 2nd userform will not respond

At the moment I have 4 sheets - the 1st form is opened from a button
which is present on both split sheets. I fill in the textboxes and then
use 'with activesheet' to populate cells in the split sheet.
Once its done this I close form 1 and open form 2 - this is where I test to
see what the name of active sheet is and if its EUFSPLIT I want the contents of form 2 to populate the paired sheet EUFRUN (same for CMF). I have 15 other pairs to set up with the 2 forms but can't get past this bit

Jon

[georgiboy]

Have a look at this...

[blackie42]

Thanks GB - yours does work - the theory is sound but when I
adapt your code to my workbook it doesn't do the same. There are other links between the 2 sheets which may be causing problems.

I'll start again and see if I can get it to work without the other links.

I'll let you know

regards

Jon