I remembered one other thing I was having difficulty with the
navigation buttons... I am using a form for each tab that collects
data then when I press the save command button it saves the data to
the active worksheet... Additionally, as I navigate the worksheet (go
to next row, previous, last, first, etc using the navigation buttons)
the form shows the data saved in that row of the worksheet or alerts
you to being at the last or first row of the worksheet... I can then
edit the data and either save it or cancel the save...
To navigate the worksheet, I have a text object called RowNumber.
RowNumber is used to identify the active row and then display the
data in that row on the form. Here is part of the code for
identifying where rows are:
Private Function FindLastRow()
Dim R As Long
R = 2
Do While R < 65536 And Len(Cells(R, 1).Text) > 0
R = R + 1
Loop
FindLastRow = R
End Function
FindLastRow is a function to identify the last row in the active
worksheet, LastRow is the last row on the worksheet that has data...
When I go to the next record say... I use:
Public Function NextRecord()
Dim R As Long
If IsNumeric(RowNumber.Text) Then
R = CLng(RowNumber.Text)
R = R + 1
If R > LastRow Then
MsgBox ("You have reached the end of the database")
ElseIf R > 1 And R <= LastRow Then
RowNumber.Text = FormatNumber(R, 0)
End If
End If
End Function
So RowNumber is updated with the row number from R....and that
identfies the row...
I have setup the form so that the navigation buttons are at the
bottom of the userform not on a specific tab. I cannot get the
RowNumber object to display the row for the active worksheet...I have
the following code to activate the worsksheet as I change form by
clicking on the tab of the multipage form:
Private Sub MultiPage1_Change()
If MultiPage1.Value = 0 Then
Set ws = Worksheets("SiteInfo")
ws.Activate
LastRow = FindLastRow
GetData
ElseIf MultiPage1.Value = 1 Then
Set ws = Worksheets("SubSiteInfo")
ws.Activate
LastRow = FindLastRow
GetSSData
ElseIf MultiPage1.Value = 2 Then
Set ws = Worksheets("CallerInfo")
ws.Activate
LastRow = FindLastRow
GetSCData
End If
End Sub
How do I get the RowNumber object to take on the number of the
current worksheet? I would appreciate your insight... Hopefully its'
not too obvious...