userform record search

I've just written a new userform to input data into an invoice database.
I would like to be able to scroll the entire database, record to record, in order to make changes. I do this everyday in my lay-a-way database, using excel's built-in userform, but don't know how to replicate this for the new form. My new database has more than 32 fields, so I can't use the built-in.

G'day,

I guess you need a SpinButton and the related code.
First you have to put a SpinButton control on to your form. Then you can try the following codes:
----------
Private Sub SpinButton1_SpinUp()
11 If ActiveCell.Row = Ofs + 1 Then GoTo 22
ActiveCell.Offset(-1, 0).Select
If ActiveSheet.Rows(ActiveCell.Row).Hidden = True Then GoTo 11
ScrollBar1.Value = ActiveCell.Row - Ofs - 1 'ScrollBar1.Value - 1
22 End Sub

Private Sub SpinButton1_SpinDown()
11 If ActiveCell.Row = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row Then GoTo 22
ActiveCell.Offset(1, 0).Select
If ActiveSheet.Rows(ActiveCell.Row).Hidden = True Then GoTo 11
ScrollBar1.Value = ActiveCell.Row - Ofs - 1 'ScrollBar1.Value + 1
22 End Sub
--------------

Explanations / pre-requisites:

1. Ofs is a variale that is defined on the Form level, the value is set in the Private Sub UserForm_Initialize() macro, the value represents the number of rows the heading of the data table is occupying at the top. So you need to write on the top of the code-page of the UserForm:

Dim i, Ofs As Integer

2. The procedure that is shown above works only if the update of the data table from the UserForm entries are triggered by the change of the ScrollBar. So you need a ScrollBar1 for this to work. And you need a procedure something like this:

Private Sub ScrollBar1_Change()
If Not ScrollBar1.Value = ActiveCell.Row - Ofs - 1 Then Cells(ScrollBar1.Value + Ofs + 1, 1).Select
FillForm
End Sub

Private Sub FillForm()
i = 1
For Each c In Me.Controls
If TypeName(c) = "TextBox" Or TypeName(c) = "ComboBox" Then
c.ControlSource = ActiveSheet.Cells(ActiveCell.Row, i).Address
i = i + 1
ElseIf TypeName(c) = "OptionButton" Or TypeName(c) = "CheckBox" Then
c.ControlSource = ActiveSheet.Cells(ActiveCell.Row, i).Address
i = i + 1
End If
Next
End Sub

3. The UserForm has to be initialized with this code:

Private Sub UserForm_Initialize()
Ofs = 1 '<<< Write here the number of rows the table heading occupies at the top of the worksheet
ActiveCell.Offset(0, 1 - ActiveCell.Column).Select
If ActiveCell.Row < Ofs + 1 Then ActiveCell.Offset(Ofs + 1 - ActiveCell.Row, 0).Select
ScrollBar1.Value = ActiveCell.Row - Ofs - 1
If ScrollBar1.Value = 0 Then FillForm
ScrollBar1.Max = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row - Ofs - 1
FillCB 'this is responsible for populating the ComboBoxes
'FillLabels 'this is responsible for populating the Labels
End Sub

4. You need to write a FillCB macro, like this:

Private Sub FillCB()
With ComboBox1
.AddItem ("Dog")
.AddItem ("Cat")
.AddItem ("Coati")
End With
With ComboBox2
.AddItem ("Genious")
.AddItem ("Average")
.AddItem ("Hopeless")
End With
With ComboBox3
.AddItem ("Espresso")
.AddItem ("Long-black")
.AddItem ("Dirty water")
End With
End Sub

5. Please note acc. to the codes above the ActiveControls will be populated in their sequence of creation. THIS IS A VERY IMPORTANT CONDITION and I haven't found a way around this, I'd appreciate any help on this one.

Please note the above is an extract of a working solution, but no guarantee this will work since this extract has not been tried. There are lots of other functionalities (PrintForm, Save button, Attach picture, etc.) that are excluded here.


Gábor