Specifying activesheets to multiple buttons....possible?

[marty6]

Hi,

I am working with a multiple page userform. On page one, I have th
following command buttons:

Enter (sheet 1)
Enter (sheet 2)

The first button inputs information into sheet 1 and the 2nd butto
"enter sheet 2" inputs information onto sheet 2.

On page two of the multiple page userform, I have the followin
buttons:

Update
Next Record
Previous Record

question: Is there a way to specify that only "next record" an
"Previous record" buttons only work with Sheet 2? When I started usin
the multipage form, and used the 2nd multpage buttons and not realizin
that sheet 1 was being displayed in the textboxes. This sound
strange. But when sheet 1 is up and running, and I am using th
multipage 2 buttons, sheet 1 information is appearing...I really don'
want that to happen.

Here's the coding for the Next and Previous buttons:

Private Sub CommandButton5_Click() [for the next button]

Dim lastRow As Long
If ActiveCell.Column <> 1 Then
Cells(ActiveCell.Row, 1).Select
End If
If ActiveCell.Row <> lastRow Then
ActiveCell.Offset(1, 0).Select
TextBox18.Text = ActiveCell.Value
TextBox19.Text = ActiveCell.Offset(0, 1).Value
TextBox20.Text = ActiveCell.Offset(0, 2).Value
TextBox21.Text = ActiveCell.Offset(0, 3).Value
TextBox22.Text = ActiveCell.Offset(0, 4).Value
TextBox23.Text = ActiveCell.Offset(0, 5).Value
TextBox24.Text = ActiveCell.Offset(0, 6).Value
TextBox25.Text = ActiveCell.Offset(0, 7).Value
TextBox26.Text = ActiveCell.Offset(0, 8).Value
TextBox27.Text = ActiveCell.Offset(0, 9).Value
End If
End Sub



Private Sub CommandButton6_Click() [for the previous button]
If ActiveCell.Column <> 1 Then
Cells(ActiveCell.Row, 1).Select
End If
If ActiveCell.Row <> 1 Then
ActiveCell.Offset(-1, 0).Select
TextBox18.Text = ActiveCell.Value
TextBox19.Text = ActiveCell.Offset(0, 1).Value
TextBox20.Text = ActiveCell.Offset(0, 2).Value
TextBox21.Text = ActiveCell.Offset(0, 3).Value
TextBox22.Text = ActiveCell.Offset(0, 4).Value
TextBox23.Text = ActiveCell.Offset(0, 5).Value
TextBox24.Text = ActiveCell.Offset(0, 6).Value
TextBox25.Text = ActiveCell.Offset(0, 7).Value
TextBox26.Text = ActiveCell.Offset(0, 8).Value
TextBox27.Text = ActiveCell.Offset(0, 9).Value
End If
End Sub



Any and all help is appreciated.

thanks,

marty

 

[Nigel]

The following template might help. If you do disable or hide controls
you'll need to reverse the action.

If ActiveSheet.Name = "Sheet2" Then
'code to populate your user form goes here
' code to enable or hide controls as well if needed
Else
'if you wish you could disable controls
TextBox1.Enabled = False
'or hide them
TextBox1.Visible = False
End If

Cheers
Nigel

 

[marty6]

Hi Nigel,

Would I place the "If ActiveSheet.name="Sheet2" Then right after th
Private Sub CommandButtons for each script? I really don't want t
disable or hide anything. I have not tried this script out, I'm a
work right now and won't be at home to try this until after 9:00p
tonight. I also place an End if at the end of each script before th
End Sub line.



Private Sub CommandButton5_Click() [for the next button]
If ActiveSheet.Name = "Sheet2" Then

Dim lastRow As Long
If ActiveCell.Column <> 1 Then
Cells(ActiveCell.Row, 1).Select
End If
If ActiveCell.Row <> lastRow Then
ActiveCell.Offset(1, 0).Select
TextBox18.Text = ActiveCell.Value
TextBox19.Text = ActiveCell.Offset(0, 1).Value
TextBox20.Text = ActiveCell.Offset(0, 2).Value
TextBox21.Text = ActiveCell.Offset(0, 3).Value
TextBox22.Text = ActiveCell.Offset(0, 4).Value
TextBox23.Text = ActiveCell.Offset(0, 5).Value
TextBox24.Text = ActiveCell.Offset(0, 6).Value
TextBox25.Text = ActiveCell.Offset(0, 7).Value
TextBox26.Text = ActiveCell.Offset(0, 8).Value
TextBox27.Text = ActiveCell.Offset(0, 9).Value
End If
End If
End Sub



Private Sub CommandButton6_Click() [for the previous button]
If ActiveSheet.Name = "Sheet2" Then

If ActiveCell.Column <> 1 Then
Cells(ActiveCell.Row, 1).Select
End If
If ActiveCell.Row <> 1 Then
ActiveCell.Offset(-1, 0).Select
TextBox18.Text = ActiveCell.Value
TextBox19.Text = ActiveCell.Offset(0, 1).Value
TextBox20.Text = ActiveCell.Offset(0, 2).Value
TextBox21.Text = ActiveCell.Offset(0, 3).Value
TextBox22.Text = ActiveCell.Offset(0, 4).Value
TextBox23.Text = ActiveCell.Offset(0, 5).Value
TextBox24.Text = ActiveCell.Offset(0, 6).Value
TextBox25.Text = ActiveCell.Offset(0, 7).Value
TextBox26.Text = ActiveCell.Offset(0, 8).Value
TextBox27.Text = ActiveCell.Offset(0, 9).Value
End If
End If
End Sub



Thanks,

marty

 

[Nigel]

marty6

Certainly, if you wrap the code that populates the userform controls with
the test that you are on sheet2 then that should work.

Not sure what your downstream processes are but if the user has a choice to
select the sheet (from page1 of the multipage?) they might expect something
to happen on page 2, which of course will not if the selected sheet is
something other that sheet2.

You might want to issue a warning, message or something to let the user
know. Hence my suggestion to disable the controls, since in Windows
interfaces controls are contextually disabled to set the right expectation.

Good luck

Cheers
Nigel

 

[marty6]

Nigel,

The additional lines that I added did not work here at work. Is ther
another way to add those lines?

marty

 

[Nigel]

What message do you get ?

Is the sheet2 actually named "Sheet2" on the Tab ? - if not change the
reference in the code to what it is called.

Cheers
Nigel

 

[marty6]

Hi Nigel,

I get the following error message:

Run-time error '424'

Object required

Any ideas?

marty

 

[marty6]

Nigel,

I got it to work. Thanks for the help!

marty