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 mlLastScrollValue As Long
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:
Private Sub cmdClose_Click()
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
Paul H. Mayfield:
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!
9 September 2004, 11:06 amJon Peltier:
Only one ‘m’ in Jimi.
- Jon
9 September 2004, 12:52 pmDick:
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.
9 September 2004, 2:18 pmfranck:
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
18 October 2004, 8:30 amStefan van DIjk:
Ola!
3 February 2005, 6:31 pmThanx 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
Lamar Atkinson:
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…
13 February 2006, 9:48 pmDick Kusleika:
Lamar: The link to the zip file is the last line of this post (right under the image.)
14 February 2006, 8:18 amLamar:
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
22 March 2006, 9:29 pmFrameTek:
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
4 April 2006, 11:14 pmPaul V
FrameTek:
all good mate! vb was importing the sheets as classes
5 April 2006, 12:24 amMisty:
Great information. Can’t download Link UserForm.zip. It’s password protected. Is it possible to see the file for part VI as well?
23 September 2007, 9:42 amDick Kusleika:
Misty: Thanks for the heads-up. I had some permissions problems, but they’re sorted now.
24 September 2007, 6:43 pm