Macro to run if cell empty
 

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" wrote in message
...
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

Macro to run if cell empty
 

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" wrote in message
...
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

Macro to run if cell empty
 

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" wrote in message
...
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

Macro to run if cell empty
 

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" wrote in message
...
Thanks I thought that could be the way to do that.

Thanks again

Greg

Macro to run if cell empty
 

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" wrote in message
...
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" wrote in message
...
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

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

Macro to run if cell empty
 

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

Macro to run if cell empty
 

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

Thanks again

Greg

Macro to run if cell empty
 

"rcalvanese" wrote in message
...

"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

Macro to run if cell empty
 

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

Greg

Macro to run if cell empty
 

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

Macro to run if cell empty
 

Thanks for that

It works well too

Greg

Macro to run if cell empty
 

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" wrote in message
...

"rcalvanese" wrote in message
...

"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

Macro to run if cell empty
 

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" wrote in message
...
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" wrote in message
...

"rcalvanese" wrote in message
...

"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

Macro to run if cell empty
 

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_" wrote in message
...
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" wrote in message
...
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" wrote in message
...

"rcalvanese" wrote in message
...

"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

Macro to run if cell empty
 

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" wrote in message
...
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