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,

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 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
> >
> >
> >
> >
>
>
>

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,
"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