Linking Userforms and Worksheets: Part II
See Part I: Setting Up the Form
In this part, I discuss helper procedures. There are two primary reasons I use helper procedures: First, there is certain code that needs to be run from various places. Rather than retype it in those places, I can simply call a procedure to do the work. Second, calling procedures that are well named makes the code more self documenting.
Loading Up the Form
Dim lRow As Long
Dim ctlInfo As Control
'Store row of current record
lRow = Me.scbContact.Value
With wksContacts.Range("A1")
'Loop through controls
For Each ctlInfo In Me.Controls
'If the Tag is numeric, it is a data entry
'control.
If IsNumeric(ctlInfo.Tag) Then
'Get the data from the worksheet
ctlInfo.Text = .Offset(lRow, ctlInfo.Tag).Value
End If
Next ctlInfo
End With
'Mark this record as clean
Me.IsDirty = False
End Sub
This procedure reads the data from the worksheet and populates the controls of the userform. I’ve used the Tag property to store the offset from column A for each field. I test IsNumeric so I can later add Tags to other forms should the need arise. If I remember to only use strings, I shouldn’t have to change this code for any tags added to non-data entry controls. I will be discussing the IsDirty line in a future post.
Returning Data to the Worksheet
Dim lRow As Long
Dim ctlInfo As Control
'Store row of current record
lRow = Me.scbContact.Value + lOffset
With wksContacts.Range("A1")
'Loop through controls
For Each ctlInfo In Me.Controls
'Limit to data entry controls
If IsNumeric(ctlInfo.Tag) Then
'Write the values to cells
.Offset(lRow, ctlInfo.Tag).Value = ctlInfo.Text
End If
Next ctlInfo
End With
'Re-initialize the scrollbar settings
DefineScroll
'Mark this record as clean
Me.IsDirty = False
End Sub
This does pretty much the same thing as the above procedure, just in reverse. I also redefine the scrollbar whenever I write to the sheet so it stays in sync with the records on the sheet.
Redefining the Scrollbar
Dim rBottom As Range
Dim lRecordCnt As Long
With wksContacts
'Find the last used cell in column A
Set rBottom = .Range("A" & .Rows.Count).End(xlUp)
'If the database is empty
If rBottom.Row = 1 Then
lRecordCnt = 1 'set for one record - a new one
Else
'Set for all records plus a new one
lRecordCnt = .Range("A2", rBottom).Rows.Count + 1
End If
End With
'Set the min and max
Me.scbContact.Min = 1: Me.scbContact.Max = lRecordCnt
End Sub
Whenever I write data to the sheet or when the form is first opened, I want my scrollbar’s Min and Max properties to be properly set. This makes sure that all records are accessible from the form and that the form has room for a new record.
can you please send me the whole VBA above. Its forms and codes. or send me the link so I can just download it. I will really much appreciate it. Thanks in advance
venus: You can download the workbook from here http://www.dailydoseofexcel.com/archives/2004/09/09/linking-userforms-and-worksheets-part-v/
I have set up a userform. On the userform the user has to fill in it’s data. That data should be written on a worksheet, in which the data will be calculated and the result should be shown in a messagebox wich has to appear after the user clicks at calculate on the userform. Could you help me out of this or give me some tips? Thanks in advance.
Hi
I have created a userform that doesn’t fit on the page when in play mode. I have installed a scroll bar so you can go down and fill in the parts but it does not work? Can you help?
Also I have installed code to resize frames if a certain check box is ticked how do I include an exception? Pls find code below.
Private Sub CheckBox2_Change()
If CheckBox2.Value = True Then
Frame7.Visible = True
Else
CheckBox2.Value = False
Frame7.Visible = False
End If
If CheckBox2.Value = True Then
Frame7.Top = 240
End If
End Sub