Linking Userforms and Worksheets: Part V
Part I: Setting Up the Form
Part II: Helper Procedures
Part III: Determining If the Form Needs to be Saved
Part IV: The Initialize Event
In this final part, I discuss the change event for the scrollbar. The scrollbar is the means by which the user moves between records. It doesn’t have to be a scrollbar, of course, but it’s one option. We’ll need one more module level variable:
Private Sub scbContact_Change()
Dim sPrompt As String
Dim sTitle As String
Dim lResp As Long
sPrompt = "Save Changes"
sTitle = "Record Has Changed"
If Me.IsDirty Then
lResp = MsgBox(sPrompt, vbYesNo, sTitle)
If lResp = vbYes Then
SaveRecord CLng(Me.scbContact.Value > mlLastScrollValue)
End If
End If
PopulateRecord
mlLastScrollValue = Me.scbContact.Value
End Sub
This procedure checks to see if the current record needs to be saved and prompts the user if it does. Then it populates the record based on the current value of the scrollbar. The module level variable holds the prior scrollbar value so the SaveRecord procedure can determine if the user is scrolling up or down. Basically, it has to know whether to save the previous or the next record because the value of the scrollbar has already changed.
Finally, the two commandbuttons have these procedures in their click events:
Unload Me
End Sub
Private Sub cmdSave_Click()
If Me.IsDirty Then
SaveRecord
End If
End Sub
Here’s what the final userform looks like
And you can download it here: LinkUserform.zip
Thanks for the zip file. The multiple posts were getting a little hard to follow, so it helps to see all the pieces put together.
Thanks, too, for your great site!
Only one ‘m’ in Jimi.
- Jon
Man, I was so worried about spelling Duluth wrong (not enough to look it up, mind you) that I didn’t even think about Jimi. I was pretty confident with Bob, though.
Hi All,
Could you please help me I wouldn’t use a scroll object but that all datas change when I change the ID/Key control change thanks in advance. Franck
Ola!
Thanx for the tips.
Till yesterday I was a nitwith in Vb.
Now I understand your program and comments.
It’s perfect for the further development of the program I want to make!
Greetz Stefan
Hi
I have been looking at your code and the responses from users. I noted that one mentioned a zip file of this code. Is this still available? Like the poster, I am having difficulty putting all this together.
Thanks…
Lamar: The link to the zip file is the last line of this post (right under the image.)
ick - Thanks for the reply. I have been busy on another project but now I am trying to get back to ‘my’ stuff!
Many thanks - I hope I can make a go of this.
Lamar
bloody sensational!
my god have I been looking for this one
I have added it to a little project of mine and straight up I get a
runtime error 1004
Method ‘Range’ of object’_Worksheet’ failed
has me stumped but will keep pluggin away at it
any advice on this is appreciated
cheers
Paul V
all good mate! vb was importing the sheets as classes
Great information. Can’t download Link UserForm.zip. It’s password protected. Is it possible to see the file for part VI as well?
Misty: Thanks for the heads-up. I had some permissions problems, but they’re sorted now.
Dick,
I’m having trouble with the enable of the Save command button. If I call up the userform using the F5 it works fine. If I create a subroutine from a general module and load the UContact form from there, the enable functionality seems to break. I can’t seem to figure out the issue. Any ideas?
Thanks,
Gary
I think I figured out the issue in the previous post. Some sort of scope / instanciation issue. I changed the references in the class module to object.parent rather than the form name and it seems to work. I guess when I used the FormName.Property syntax it must have been creating a new instance of the form and manipulating it’s properties, not the form that called it.
Gary
Gary: That’s right. Thanks to Jan Karel Pieterse and Andy Pope for helping find this answer. Change the code in CControlEvents to
gCombo.Parent.IsDirty = True
End Sub
Private Sub gTextBox_Change()
gTextBox.Parent.IsDirty = True
End Sub