| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Specifying activesheets to multiple buttons....possible?
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 -- Message posted from http://www.ExcelForum.com |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Specifying activesheets to multiple buttons....possible?
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 " wrote in message ... Hi, I am working with a multiple page userform. On page one, I have the following command buttons: Enter (sheet 1) Enter (sheet 2) The first button inputs information into sheet 1 and the 2nd button "enter sheet 2" inputs information onto sheet 2. On page two of the multiple page userform, I have the following buttons: Update Next Record Previous Record question: Is there a way to specify that only "next record" and "Previous record" buttons only work with Sheet 2? When I started using the multipage form, and used the 2nd multpage buttons and not realizing that sheet 1 was being displayed in the textboxes. This sounds strange. But when sheet 1 is up and running, and I am using the multipage 2 buttons, sheet 1 information is appearing...I really don't 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, marty6 --- Message posted from http://www.ExcelForum.com/ |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Specifying activesheets to multiple buttons....possible?
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 -- Message posted from http://www.ExcelForum.com |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Specifying activesheets to multiple buttons....possible?
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 " wrote in message ... Hi Nigel, Would I place the "If ActiveSheet.name="Sheet2" Then right after the Private Sub CommandButtons for each script? I really don't want to disable or hide anything. I have not tried this script out, I'm at work right now and won't be at home to try this until after 9:00pm tonight. I also place an End if at the end of each script before the 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, marty6 --- Message posted from http://www.ExcelForum.com/ |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Specifying activesheets to multiple buttons....possible?
Nigel,
The additional lines that I added did not work here at work. Is ther another way to add those lines? marty -- Message posted from http://www.ExcelForum.com |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Specifying activesheets to multiple buttons....possible?
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 " wrote in message ... Nigel, The additional lines that I added did not work here at work. Is there another way to add those lines? marty6 --- Message posted from http://www.ExcelForum.com/ |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Specifying activesheets to multiple buttons....possible?
Hi Nigel,
I get the following error message: Run-time error '424' Object required Any ideas? marty -- Message posted from http://www.ExcelForum.com |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Specifying activesheets to multiple buttons....possible?
Nigel,
I got it to work. Thanks for the help! marty -- Message posted from http://www.ExcelForum.com |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| buttons bleeding onto multiple pages | Setting up and Configuration of Excel | |||
| enable/disable multiple buttons | Excel Discussion (Misc queries) | |||
| renaming multiple buttons | Excel Discussion (Misc queries) | |||
| Deleting multiple Macro buttons | Excel Discussion (Misc queries) | |||
| Multiple fill color buttons | Excel Discussion (Misc queries) | |||
![marty6[_12_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
