Macro to run if cell empty

Is it possible to run a certain macro if a cell does not have any
information in it?

If it can be done how?

Greg

You can use event code to trap if a cell becomes empty and run a macro, or
if something else on the sheet causes recalculation, then check that cell
for empty and run the macro (but this could fire it many times).

What exact situation do you want?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Greg" <laptopgb@ihug.com.au> wrote in message
news:d2ecop$b39$1@lust.ihug.co.nz...
> Is it possible to run a certain macro if a cell does not have any
> information in it?
>
> If it can be done how?
>
> Greg
>
>

I wanted to run a userform once when this workbook is loaded for the first
time. I needed the user to place some information information in this form.
Once this userform is used I did not want it to load up again.

Not sure how to do this exactly though maybe it could look up the cell where
this information would be placed. If empty run a macro and then once the
form has been used it will have the information in the cell so it would not
be run again.

Thanks again any help is appreciated

Greg

Greg,

You could use workbook_open then, something like


Private Sub Workbook_Open()
If Worksheets("Sheet1").Range("A1") = "" Then
Userform1.Show
Worksheets("Sheet1").Range("A1") = "X"
Else
'do something else
End If
End Sub


'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code



--

HTH

RP
(remove nothere from the email address if mailing direct)


"Greg" <laptopgb@ihug.com.au> wrote in message
news:d2edpq$dft$1@lust.ihug.co.nz...
> I wanted to run a userform once when this workbook is loaded for the first
> time. I needed the user to place some information information in this
form.
> Once this userform is used I did not want it to load up again.
>
> Not sure how to do this exactly though maybe it could look up the cell
where
> this information would be placed. If empty run a macro and then once the
> form has been used it will have the information in the cell so it would
not
> be run again.
>
> Thanks again any help is appreciated
>
> Greg
>
>
>
>

Thanks I thought that could be the way to do that.

Thanks again

Greg

Greg,

Put the following in the ThisWorkbook code module:

Private Sub Workbook_Open()
On Error Resume Next
Dim NameExists As Boolean
If Me.Worksheets("SHeet1").Range("A1").Value = "" Then
NameExists = CBool(Len(ThisWorkbook.Names("RunOnce").Name))
If NameExists = False Then
UserForm1.Show
ThisWorkbook.Names.Add Name:="RunOnce", RefersTo:="Yes"
End If
End If
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com





"Greg" <laptopgb@ihug.com.au> wrote in message
news:d2edpq$dft$1@lust.ihug.co.nz...
>I wanted to run a userform once when this workbook is loaded for
>the first
> time. I needed the user to place some information information
> in this form.
> Once this userform is used I did not want it to load up again.
>
> Not sure how to do this exactly though maybe it could look up
> the cell where
> this information would be placed. If empty run a macro and then
> once the
> form has been used it will have the information in the cell so
> it would not
> be run again.
>
> Thanks again any help is appreciated
>
> Greg
>
>
>
>

Greg,

Have you checked your clock, it is some 17-18 hours ahead.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Greg" <laptopgb@ihug.com.au> wrote in message
news:d2eein$ekr$1@lust.ihug.co.nz...
> Thanks I thought that could be the way to do that.
>
> Thanks again
>
> Greg
>
>

Thanks Bob will check the clock out just formatted my whole system never
thought about the system clock

Greg

Seems to be right it is saying I sent my prevbious message at exactly the
time I sent it. I usually do this work at home at night. So I dont think I
am missing anything.

Am I missing something I checked the BIOS clock and also the regional
settings all are current and reading the right time for here.

Thanks

Greg

Greg,
"On Error Resume Next" is not a good programming practice. Also the code is
over kill for what you want to do. You do not need to declare a bool and
then cast the cells contents into another bool to compare to the declared
bool. You only need to check to see if the cell has something in it.

Put this code into the On_Click event on the Forms CommandButton1. If you
want to validate what is entered on the form like numbers and such... you
will have to add code to do that.

Private Sub CommandButton1_Click()
Worksheets(1).Cells(1, 1).Value = Me.TextBox1.Text
ThisWorkbook.Save
End
End Sub

Add the following code to the Workbook's On_Load event. I have added a
simple error handler as well. You want to "handle erreors" not "ignore
them".

Private Sub Workbook_Open()
On Error GoTo ErrorHandler
If Worksheets(1).Cells(1, 1).Value = "" Then
UserForm1.Show Modal
End If
Exit Sub
ErrorHandler:
MsgBox Err.Description
End Sub

Let me know if this helps.
Bob Calvanese
"Chip Pearson" <chip@cpearson.com> wrote in message
news:%236pb1kTNFHA.1096@tk2msftngp13.phx.gbl...
> Greg,
>
> Put the following in the ThisWorkbook code module:
>
> Private Sub Workbook_Open()
> On Error Resume Next
> Dim NameExists As Boolean
> If Me.Worksheets("SHeet1").Range("A1").Value = "" Then
> NameExists = CBool(Len(ThisWorkbook.Names("RunOnce").Name))
> If NameExists = False Then
> UserForm1.Show
> ThisWorkbook.Names.Add Name:="RunOnce", RefersTo:="Yes"
> End If
> End If
> End Sub
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
>
>
>
> "Greg" <laptopgb@ihug.com.au> wrote in message
> news:d2edpq$dft$1@lust.ihug.co.nz...
>>I wanted to run a userform once when this workbook is loaded for the first
>> time. I needed the user to place some information information in this
>> form.
>> Once this userform is used I did not want it to load up again.
>>
>> Not sure how to do this exactly though maybe it could look up the cell
>> where
>> this information would be placed. If empty run a macro and then once the
>> form has been used it will have the information in the cell so it would
>> not
>> be run again.
>>
>> Thanks again any help is appreciated
>>
>> Greg
>>
>>
>>
>>
>
>

Thanks for that

It works well too

Greg

"rcalvanese" <bcalvanese@comcast.net> wrote in message
news:CdednQVf1tu9yNbfRVn-hg@comcast.com...

> "On Error Resume Next" is not a good programming practice.

That is far too general a statement.

> You want to "handle erreors" not "ignore them".

In many cases, On Error Resume Next is helping to handle errors. For example

On Error Resume Next
Set sh = Worksheets("somename")
On Error Goto 0
If sh Is Nothing Then
Worksheets.Add.Name = "somename"
End If

When I went to school, If we used On Error Resume Next anywhere in our code,
we had points taken off our GPA. Some people may have a different oppinion,
but in my oppinion... It is a poor programing practice. And any place that
I've worked so far that does VB/VBA looks down upon it as well. In fact...
You are the first person I've run accross who seems to think other wise.

Best Regards,

Bob Calvanese

"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:uTjgS5cNFHA.2736@TK2MSFTNGP09.phx.gbl...
>
> "rcalvanese" <bcalvanese@comcast.net> wrote in message
> news:CdednQVf1tu9yNbfRVn-hg@comcast.com...
>
>> "On Error Resume Next" is not a good programming practice.
>
> That is far too general a statement.
>
>> You want to "handle erreors" not "ignore them".
>
> In many cases, On Error Resume Next is helping to handle errors. For
> example
>
> On Error Resume Next
> Set sh = Worksheets("somename")
> On Error Goto 0
> If sh Is Nothing Then
> Worksheets.Add.Name = "somename"
> End If
>
>

I agree with Bob Phillips on this point. I just recently got into the exact
same discussion on a VB/VB.Net form. ALL error handlers are prone to being
"not a good programming practice" if not coded correctly. And furthermore,
if MS decided to include "On Error Resume Next" compatability in Dot Net
2003, but not provide a "Try....Catch" equivalent, then there's browney
points too. Here's an example:

Sub ShowBadErrorPractice()
On Error GoTo ErrorHandler
Set NonExistantSheet = Worksheets("ThisWorkSheetDoesntExist")
' BLAH
' BLAH
' BLAH
' BLAH
Exit Sub

ErrorHandler:
Select Case Err.Number
Case 1000
' BLAH
Case 1001
' BLAH
Case 1002
' BLAH
End Select
' WHAT@!? No error 9 handling???
End Sub

The best example of the use of "On Error Resume Next" which I use a lot of
is with Dynamic Ranges:

On Error Resume Next
If lbWS.Tag = "Select All" Then
Worksheets("Summary").Range("DataRange").Clear
' Error 1004 "Application-defined or object-defined error" will occur if
the
' Summary ws is already clear and the name range defines to an illegal
address (it's dynamic!)
On Error GoTo 0

Now, show me a better method of clearing a dynamic range using not more than
two lines of code!

Every Error routine is only as good as the person who programmed it. If you
are going to use an "On Error Resume Next", it has got to be placed right
where you expect the error to occur and right after the code which "could"
produce an error, you need to have you error checking/correcting code.

--
|
+--Thief_
|


"rcalvanese" <bcalvanese@comcast.net> wrote in message
news:DKSdnQdVTJu9VtHfRVn-og@comcast.com...
> When I went to school, If we used On Error Resume Next anywhere in our
code,
> we had points taken off our GPA. Some people may have a different
oppinion,
> but in my oppinion... It is a poor programing practice. And any place that
> I've worked so far that does VB/VBA looks down upon it as well. In fact...
> You are the first person I've run accross who seems to think other wise.
>
> Best Regards,
>
> Bob Calvanese
>
> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> news:uTjgS5cNFHA.2736@TK2MSFTNGP09.phx.gbl...
> >
> > "rcalvanese" <bcalvanese@comcast.net> wrote in message
> > news:CdednQVf1tu9yNbfRVn-hg@comcast.com...
> >
> >> "On Error Resume Next" is not a good programming practice.
> >
> > That is far too general a statement.
> >
> >> You want to "handle erreors" not "ignore them".
> >
> > In many cases, On Error Resume Next is helping to handle errors. For
> > example
> >
> > On Error Resume Next
> > Set sh = Worksheets("somename")
> > On Error Goto 0
> > If sh Is Nothing Then
> > Worksheets.Add.Name = "somename"
> > End If
> >
> >
>
>

Everyone is entitled to their opinion, and now, you are the second person
that I've come accross that thhinks differently. I imagine there are
probably many others who would use it as well.

My definition of this is simple...
On Error... If an error occurs
Resume Next... Don't worry about it
To me (IMHO) equates to... Poor Programming Practices.

Lets say you are working on a large system and this was allowed to be used
by all who worked on the system (bad idea). Now lets say that it ignored an
error that caused the system to not correctly calculate something. Now lets
say this calculation was something that people made business decisions on,
or this was information that you are providing to your clients and expected
to be correct. You may have several developers working on this system all
using this practice.

If you do not see the potential for disaster here... I don't know what more
I can say. Sure a properly handled exception is important, but even a poorly
handled exception would at least be trapped and display something to the
user. And that is much better than having an exception that no one knows
about until it becomes a disaster.

I have seen it happen, and understand why it is looked down upon. This is
just my opinion, but I think I'll stick with it. Those who choose to use it
have every right to do so, but when it comes back and takes a big bite out
of your butt... Don't say I did'nt try to warn ya...

Best Regards
Bob Calvanese

"Thief_" <thief_@hotmail.com> wrote in message
news:uGhmaVnNFHA.3560@TK2MSFTNGP14.phx.gbl...
>I agree with Bob Phillips on this point. I just recently got into the exact
> same discussion on a VB/VB.Net form. ALL error handlers are prone to being
> "not a good programming practice" if not coded correctly. And furthermore,
> if MS decided to include "On Error Resume Next" compatability in Dot Net
> 2003, but not provide a "Try....Catch" equivalent, then there's browney
> points too. Here's an example:
>
> Sub ShowBadErrorPractice()
> On Error GoTo ErrorHandler
> Set NonExistantSheet = Worksheets("ThisWorkSheetDoesntExist")
> ' BLAH
> ' BLAH
> ' BLAH
> ' BLAH
> Exit Sub
>
> ErrorHandler:
> Select Case Err.Number
> Case 1000
> ' BLAH
> Case 1001
> ' BLAH
> Case 1002
> ' BLAH
> End Select
> ' WHAT@!? No error 9 handling???
> End Sub
>
> The best example of the use of "On Error Resume Next" which I use a lot of
> is with Dynamic Ranges:
>
> On Error Resume Next
> If lbWS.Tag = "Select All" Then
> Worksheets("Summary").Range("DataRange").Clear
> ' Error 1004 "Application-defined or object-defined error" will occur
> if
> the
> ' Summary ws is already clear and the name range defines to an illegal
> address (it's dynamic!)
> On Error GoTo 0
>
> Now, show me a better method of clearing a dynamic range using not more
> than
> two lines of code!
>
> Every Error routine is only as good as the person who programmed it. If
> you
> are going to use an "On Error Resume Next", it has got to be placed right
> where you expect the error to occur and right after the code which "could"
> produce an error, you need to have you error checking/correcting code.
>
> --
> |
> +--Thief_
> |
>
>
> "rcalvanese" <bcalvanese@comcast.net> wrote in message
> news:DKSdnQdVTJu9VtHfRVn-og@comcast.com...
>> When I went to school, If we used On Error Resume Next anywhere in our
> code,
>> we had points taken off our GPA. Some people may have a different
> oppinion,
>> but in my oppinion... It is a poor programing practice. And any place
>> that
>> I've worked so far that does VB/VBA looks down upon it as well. In
>> fact...
>> You are the first person I've run accross who seems to think other wise.
>>
>> Best Regards,
>>
>> Bob Calvanese
>>
>> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
>> news:uTjgS5cNFHA.2736@TK2MSFTNGP09.phx.gbl...
>> >
>> > "rcalvanese" <bcalvanese@comcast.net> wrote in message
>> > news:CdednQVf1tu9yNbfRVn-hg@comcast.com...
>> >
>> >> "On Error Resume Next" is not a good programming practice.
>> >
>> > That is far too general a statement.
>> >
>> >> You want to "handle erreors" not "ignore them".
>> >
>> > In many cases, On Error Resume Next is helping to handle errors. For
>> > example
>> >
>> > On Error Resume Next
>> > Set sh = Worksheets("somename")
>> > On Error Goto 0
>> > If sh Is Nothing Then
>> > Worksheets.Add.Name = "somename"
>> > End If
>> >
>> >
>>
>>
>
>

Thanks just what I needed to get my brain working

"Bob Phillips" wrote:

> Greg,
>
> You could use workbook_open then, something like
>
>
> Private Sub Workbook_Open()
> If Worksheets("Sheet1").Range("A1") = "" Then
> Userform1.Show
> Worksheets("Sheet1").Range("A1") = "X"
> Else
> 'do something else
> End If
> End Sub
>
>
> 'This is workbook event code.
> 'To input this code, right click on the Excel icon on the worksheet
> '(or next to the File menu if you maximise your workbooks),
> 'select View Code from the menu, and paste the code
>
>
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Greg" <laptopgb@ihug.com.au> wrote in message
> news:d2edpq$dft$1@lust.ihug.co.nz...
> > I wanted to run a userform once when this workbook is loaded for the first
> > time. I needed the user to place some information information in this
> form.
> > Once this userform is used I did not want it to load up again.
> >
> > Not sure how to do this exactly though maybe it could look up the cell
> where
> > this information would be placed. If empty run a macro and then once the
> > form has been used it will have the information in the cell so it would
> not
> > be run again.
> >
> > Thanks again any help is appreciated
> >
> > Greg
> >
> >
> >
> >
>
>
>