Moving around on a userform

  • Thread starter Thread starter Newbie1
  • Start date Start date
N

Newbie1

I have created a userform and I have an up/down button for moving between
records (rows)
Here is what I have:

Private Sub SpinButton1_SpinDown()

' Need error checking.... Is it the last record???
'if Not Last Record
ActiveCell.Offset(1, 0).Select
'else
' prompt if new record is to be added
'endif
FillMyFormFG

' need to display Record of Record
End Sub

Private Sub SpinButton1_Spinup()
If ActiveCell.Row > 1 Then
ActiveCell.Offset(-1, 0).Select
FillMyFormFG
else
'add in code to beep or let user know BOF has been reached
End If
' need to display Record of Record
End Sub

As you can see from the code, it is fairly simple. But I want to put error
checking in and a counter for what Record of Record. I thought I could use
counta, but I can not figure out how to in VBA.

How would this change if I have some records hidden? I will be using
different filters and would like to move properly and show the correct
record.

Thanks
 
Private Sub SpinButton1_SpinDown()
lastrow = Cells(rows.count,1).end(xlup)

do
if activecell.row = lastrow then
msgbox "At the bottom"
exit do
End if
activeCell.offset(1,0).Select
Application.Statusbar = activecell.Row & " of " & lastRow & " records
Loop while activeCell.Entirerow.Hidden = True
FillMyFormFG
if ActiveCell.row = Lastrow then
ans = msgbox "Add new record", vbYesNo
if ans = vbYes then
activecell.offset(1,0).Select
call AddRecord
end if
End if
 
Try this:

Add a userform named "Userform1". On Userform1 add Spinbutton1 an
CommandButton1. Paste this code into module 1:
sub showtest()
Userform1.show
end sub
Paste a button on your worksheet and link it to macro "showtest"

Paste this code into the declarations section:

Instead of the message boxes. insert your own code.
___________________________

Private Sub CommandButton1_Click()
UserForm1.Hide
End Sub

Private Sub SpinButton1_SpinDown()
Dim total As String
Dim current As String

current = Sheet1.Range("a1").Value
Sheet1.Range("b1").Offset(current, 0).Select
Sheet1.Range("a1").Value = Sheet1.Range("a1").Value + 1

If current >= 20 Then
MsgBox "you have reached the end. I will write code here to"
vbNewLine _
& "ask the user if they want to add a new item."
Sheet1.Range("b20").Select
Sheet1.Range("a1").Value = 20
End If
'
' need to display Record of Record
End Sub

Private Sub SpinButton1_Spinup()
current = Sheet1.Range("a1").Value
If current > 1 Then
Sheet1.Range("a1").Value = Sheet1.Range("a1").Value - 1
current = Sheet1.Range("a1").Value
Sheet1.Range("b1").Offset(current - 1, 0).Select
End If
If current <= 1 Then
MsgBox "you have reached the top"
Sheet1.Range("b1").Select
Sheet1.Range("a1").Value = 1
End If


End Sub


Private Sub UserForm_activate()
total = 20
Sheet1.Range("a1").Value = 1
Sheet1.Range("b1").Select
End Su
 
Tom,
The line
lastrow = Cells(rows.count,1).end(xlup)

returns what is in Cell A of the row. Not what the last row number is. Is
that how it should work???? That is currently a TEXT field.
Should it return a number? Correct? Or am I supposed to have a number in COL
A?
Since several of the other steps are based on the value of 'lastrow', it
does not guite work right.... Hummmm
I am not defining any range nor do I have any data "selected" (all saples I
found were looking at a given range or selection)

I made out MOST of the command, but not all.
Thanks
 
should be

lastrow = Cells(rows.count,1).end(xlup).Row

it is untested pseudo code, much like your own.
 
Thanks Tom,
That .ROW is what I needed!


Tom Ogilvy said:
should be

lastrow = Cells(rows.count,1).end(xlup).Row

it is untested pseudo code, much like your own.

--
Regards,
Tom Ogilvy


saples
Click to expand...
 
Back
Top