okay, this is my last question

viper

Active Member
Joined
Feb 15, 2002
Messages
382
What is the correct way to call a macro when an empty cell is no longer empty?

In cells C6:C10 and C13:C40 are empty right now, what I need to do is call my macro when one of those cells is no longer empty. I've tried using:
If Target.Address <> "C6:C10" Then Exit Sub
If Target.Address <> "C13:C40" Then Exit Sub
If Target.Address <> "" Then Call MacroName
And I've tried using:
If Cells("6,C") <> "" Then Call MacroName
So I don't use up space I did the above for each cell that is currently empty.

I used this code to call a macro before:
If Target.Count > 1 Then Exit Sub
If Target.Address <> "$G$11" Then Exit Sub
If UCase (Target.Value) "Word" Then Call MacroName
Which worked well as long as I entered the specified word in the specified cell. But I don't want the user to have to enter the data and then enter the word to get the macro to activate. I prefer to have the macro to be called as soonafter the new data is entered.
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Hi Viper

Try this simple code, it will only run IF the cell that is changed WAS empty AND is within the range C6:C10,C13:C40



Dim RWatch As Range
Dim strWatch As String

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("C6:C10,C13:C40")) Is Nothing Then Exit Sub
If Not RWatch Is Nothing Then MsgBox "L"
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set RWatch = Nothing
If IsEmpty(Target) Then Set RWatch = Target
End Sub
 
Upvote 0
Sorry Tab,Enter always messes me up.

Thanks for the code it worked great. But as I was trying it out and then cleared the data I realized that I need the macro to activate when any cell that is occupied becomes empty. I can extend the range in the code to also include the cells that are presently occupied but like I said I didn't think about it but I do need the macro to activate when any of the cells become vacant again.

Different subject: Is there a limit to how many worksheet open events?

I currently have one that disables the Tool>Macro and then another one that displays a splash screen(when this spreadsheet goes company wide I want my 15 min., don't worry I have added all the help I have gotten from this board and you people (just so you get yours too))but anyway, after starting my file it takes it a long time to display the splash screen. I have the userform.show code first before the other but is there any way to increase the opening?

Thanks,
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top