User form pop up if cell is empty on close workbook

  • Thread starter Thread starter Dave Potter
  • Start date Start date
D

Dave Potter

I have created a user form in a workbook. I would like the form to pop
up if a certain cell in a worksheet is empty when the user tries to
close the workbook. I dont know how to write the code in VBA. Can
anyone help with that? I am using XL2002 and WinXP Pro.
TIA for any help
Dave
 
Bob, I've taken your code and put in the sheet and range values that I
need to. In VBA, I inserted a module in the workbook and copied and
pasted the code into the module and saved file. However, It doesn't
seem to be working. I can close the workbook without being prompted by
the userform1. I did notice that a bit of code was added to the sheet
36 which contains the cell I want to reference. I know nothing about
VBA as far as how to write code. Does it sound like I did something
wrong?
 
Dave,

Make certain that you placed the code where Tom showed you.
Also make sure that you didn't leave any errant pieces of code
lying elsewhere in a regular module or one of the sheet modules.
Insure that you modify Bob's code to suit your induvidual
circumstances:
If IsEmpty(Worksheets("Sheet1").Range("A1").Value) Then
Click to expand...
"Sheet1" should be replaced with your sheet name.
UserForm1.Show
Click to expand...
"UserForm1" should be replaced with the name of your UserForm

John
 
Dave,

Ya' know as I was writing that reply I thought that I hadn't seen
the particular line of code that you're asking for now mentioned.
Was going to include it just for the heck of it in my previous
response, but alas, I forgot.

Unload UserForm1

The above placed in the "Click" event of the button on the
UserForm.

To get to the Click Event for the button, with the userform displayed,
just double click on the button itself.

John
 
To get to the Click Event for the button, with the userform displayed,
just double click on the button itself.
Click to expand...

With the userform as the activewindow in the VBE - that is what John means
by displayed.

Regards,
Tom Ogilvy
 
John,
I was able to do what you said and heres what it says:
Private Sub CommandButton1_Click()
Unload UserForm1
End Sub

Private Sub TextBox1_Change()

End Sub

However, The OK button still doesnt close the UserForm1. Does
something have to be set in the properties of the commandbutton1?
Also, You can see I added code for the TextBox1. What could I insert
there to make the text unchangeable? Currently, When the UserForm1
appears, the user can go in the text box and change the text.
 
Private Sub CommandButton1_Click()
Unload me
End Sub

Is the commandbutton actually named commandbutton1

Go into the properties of the textbox and change Locked to True.

Regards,
Tom Ogilvy
 
When I set the locked property to False and assign a value to Value in the
properties, then when the form is shown, it looks like an active textbox,
but you can't edit the value in the textbox. I believe that is what he was
trying to achieve. (not sure what you mean by seemed to work).

Clearly a Label is the more appropriate control.

Setting enabled to false makes the text in the textbox appear greyed out.

Either locked or enabled would protect the value in the textbox.

So, since he took all your advice - what were you supposed to do?
 
Tom,
(not sure what you mean by seemed to work)
Click to expand...
Take that as "seemed to work for me" in that I couldn't get it
to fail as he did. I'm very wary of saying that I'm "sure" of
something in the ng as I've made my share of mistakes.
what were you supposed to do?
Click to expand...
I just wanted to see if there was something else there that we were
missing that was causing him problems. Sometimes (at least with me),
a quick look at the workbook can save a lot of time and a lot
of dialog.
What I did was just add the button, tested it to make sure it worked,
checked for any errant code in the sheets and modules, ran Rob Bovey's
Code Cleaner on it and sent it back.
I had thought that you offered that as a reason that the button wasn't
working (and I wasn't "sure" if it had anything to do with his problem)
which is why I went and tested it on my own (and couldn't duplicate
the problem). I didn't realise that you were offering that piece of advice
as to how to protect the contents of the TextBox.

Regards,
John
 
Just wanted to say thanks to both of you for the effort put forth to
help me.Tom, sorry I didnt post back to the ng that I had made those
changes with the text box and commandbutton1. It was about that time
that I decided to send the wb to John so he could have a look. I could
have saved a few followup posts. Live and learn, huh? Well, anyway, as
a newbie around this ng I can't help but have alot of admiration for
all the folks in here who continually give of their time and knowledge
to help educate a floundering excel user like myself. To all you
folks, HATS OFF!! I can only say to those few who occassionally abuse
the real HELPERS around here, "Appreciate what you have! All these
guys have to do is hit a button on the PC and, "voila" , You're out in
the cold!!"

Thankfully,
Dave

p.s. John, how the heck'd you do that anyway? :)
 
Dave,
p.s. John, how the heck'd you do that anyway? :)
Click to expand...
I probably learned it from Tom and the rest of the
dedicated folks in the newsgroup.

You're welcome,
John
 
Back
Top