Welcome to the Excel Forum

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed.

Please Register to Remove these Ads

Please Register to Remove these Ads



Reply
  #1  
Old 06-18-2009, 06:10 PM
Directlinq Directlinq is offline
Forum Contributor
 
Join Date: 24 Oct 2008
Location: England
Posts: 146
Directlinq is becoming part of the community
If cell empty dont run macro

Please Register to Remove these Ads

How can this be edited so that if cell B5 is empty a msgbox comes up if not the macro follows through?

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Sheets("Headed Paper").Range("N1").Value = Sheets(ActiveSheet.Name).Range("A2").Value
Sheets("Headed Paper").Range("O1").Value = Sheets(ActiveSheet.Name).Range("A3").Value

Dim shSource As Worksheet, shDestination As Worksheet, iR As Long, i As Byte
Set shSource = ActiveSheet
Set shDestination = Sheets("Invoice Template") ' =Sheet4

If Target.Count > 1 Then Exit Sub
If (Not Intersect(Target, shSource.Range("J5:J500")) Is Nothing) And Target.Cells.Count = 1 Then
If UCase(Target.Value) = "Y" Then
Worksheets("Invoice Template").Visible = True
Dim aso(), aco()
aso = Array("A", "B", "C", "D", "G", "F", "H", "I")
aco = Array("B14", "B16", "F16", "D35", "D36", "D37", "D38", "D39")

iR = Target.Row
For i = 0 To UBound(aso)
Range("J" & iR).FormulaR1C1 = "DONE"
shDestination.Range(aco(i)).Value = shSource.Range(aso(i) & iR).Value
Next
With Sheets("Invoice Template")
.Range("N1").Value = Sheets(ActiveSheet.Name).Range("A2").Value
.Range("O1").Value = Sheets(ActiveSheet.Name).Range("A3").Value

End With
Range("o3").Value = ("J" & iR)
shDestination.Protect
shDestination.Select
Sheet1.Visible = False
Sheet2.Visible = False
Sheet5.Visible = False
End If
End If
Application.EnableEvents = True
End Sub
Many Thanks
Reply With Quote
  #2  
Old 06-18-2009, 06:22 PM
JBeaucaire's Avatar
JBeaucaire JBeaucaire is online now
Forum Guru
 
Join Date: 21 Mar 2008
Location: Bakersfield, CA
MS Office Version:2003 (can read 2007 files)
Posts: 8,418
JBeaucaire makes giving solutions look like childsplay JBeaucaire makes giving solutions look like childsplay JBeaucaire makes giving solutions look like childsplay JBeaucaire makes giving solutions look like childsplay JBeaucaire makes giving solutions look like childsplay JBeaucaire makes giving solutions look like childsplay JBeaucaire makes giving solutions look like childsplay JBeaucaire makes giving solutions look like childsplay JBeaucaire makes giving solutions look like childsplay
Send a message via Skype™ to JBeaucaire
Re: If cell empty dont run macro

Start of like this...
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If IsEmpty(Range("B5")) Then
    MsgBox "Cell B5 is not supposed to be empty..."
    Exit Sub
End If

....continue your macro
__________________
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
“None of us is as good as all of us” - Ray Kroc
Always put your code between [code] and [/code] tags.
Reply With Quote
  #3  
Old 06-18-2009, 06:27 PM
Phil_V Phil_V is offline
Valued Forum Contributor
 
Join Date: 23 Feb 2006
Location: Near London, England
MS Office Version:Office 2003
Posts: 752
Phil_V is attaining expert status Phil_V is attaining expert status
Re: If cell empty dont run macro

As the first line(s):

Code:
If Range("B5").Value = "" Then
    MsgBox "B5 is blank"
    Exit Sub
End If
__________________
If you find the response helpful please click the scales in the blue bar above and rate it
If you don't like the response, don't bother with the scales, they are not for you
Reply With Quote


Reply

Bookmarks


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off

Forum Jump