Macro Help

[Jacko058]

Hopefully somebody can help/guide me in the right place with two of my Macros..

Firstly I have a check to ensure a cell has valid data. So far it only checks if its zero. I need this to also check if its blank or = ""

Sub Start() If ActiveSheet.Range("E3").Value = "0" Then
 MsgBox ("Cell E3 cannot be blank")
 Exit Sub
 End If
 
Call Finish


End Sub

The next one is used to generate a popup box where the user will input their name.
I'm trying to make this so if they don't input a name or press the 'Cancel' button then the Macro's will end however it seems to be continuing!

Sub CopyData()Application.ScreenUpdating = False
   'Firstly select the LOG sheet
    Sheets("Log").Select
    
    'Then unlock
    Sheets("Log").Unprotect Password:="secret"




    Application.ScreenUpdating = False
    
    
    Dim response As String
    response = InputBox("Please enter your name.", "Enter your full name")
    If response = "" Then MsgBox ("Printing cancelled, you must enter your name")
    
    Exit Sub
    
    Sheets("Log").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) = response
    
    
    Application.ScreenUpdating = True
   
    
    Call LogEntry
     
End Sub

Thanks for the help!

 

[Joe4]

Try these modifications:

Sub Start() 

    If Len(Range("E3")) = 0 or Range("E3") = 0 Then
        MsgBox "Cell E3 cannot be blank or 0"
        Exit Sub
    End If

    Call Finish

End Sub

Sub CopyData()

    Application.ScreenUpdating = False
    'Firstly select the LOG sheet
    Sheets("Log").Select
    
    'Then unlock
    Sheets("Log").Unprotect Password:="secret"

    Dim response As String
    response = InputBox("Please enter your name.", "Enter your full name")
    If Len(response) = 0 Then 
        MsgBox "Printing cancelled, you must enter your name"
        Exit Sub
    End If

    Sheets("Log").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) = response

    Application.ScreenUpdating = True
   
    Call LogEntry
     
End Sub

 

[Jacko058]

Try these modifications:

Sub Start() 

    If Len(Range("E3")) = 0 or Range("E3") = 0 Then
        MsgBox "Cell E3 cannot be blank or 0"
        Exit Sub
    End If

    Call Finish

End Sub

Sub CopyData()

    Application.ScreenUpdating = False
    'Firstly select the LOG sheet
    [COLOR=#ff0000]Sheets("Log").Select[/COLOR]
    
    'Then unlock
    [COLOR=#ff0000]Sheets("Log").Unprotect Password:="secret"[/COLOR]

    Dim response As String
    response = InputBox("Please enter your name.", "Enter your full name")
    If Len(response) = 0 Then 
        MsgBox "Printing cancelled, you must enter your name"
        Exit Sub
    End If

    Sheets("Log").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) = response

    Application.ScreenUpdating = True
   
    Call LogEntry
     
End Sub

That works perfectly thankyou!

Just a couple more things, The code highlighted red above can I add that under the exit sub (for cleaner code so it doesn't un-protect the sheet)

Also, I have another bit of code that I need to call another macro if the user presses cancel.

Dim Sht As Worksheet    'Added for Printer Pop Up box - if user cancels then the macro stops
    If Not (Application.Dialogs(xlDialogPrinterSetup).Show) Then Exit Sub
    'need to call Macro3 if the user presses cancel

[COLOR=#ff0000]Else then run rest of code here[/COLOR]

 

[Joe4]

Just a couple more things, The code highlighted red above can I add that under the exit sub (for cleaner code so it doesn't un-protect the sheet)

Sure. Just move that whole block underneath the whole IF/THEN block.

Also, I have another bit of code that I need to call another macro if the user presses cancel.

Then just add it in.

Note that you can create other Sub Procedures that do certain tasks, and then just call them (like your did with "Finish"), if that makes it any easier.

 

[Jacko058]

Sure. Just move that whole block underneath the whole IF/THEN block.

Then just add it in.

Note that you can create other Sub Procedures that do certain tasks, and then just call them (like your did with "Finish"), if that makes it any easier.

I've tried replicating your code above but doesn't seem to work.
It either calls Macro 3 and runs the rest of the code anyways or just breaks it..

Sub Print()'
Dim Sht As Worksheet

'Added for Printer Pop Up box
    If Not (Application.Dialogs(xlDialogPrinterSetup).Show) Then Exit Sub

'''' if sub is cancelled then call Macro 3 to warn the user ! '''''
    
    'Added for Printer Pop Up box
Sheets("ET").Unprotect Password:="secret"
Do While Range("E3") > 0
Range("A1").Value = Range("A1").Value + 1
Range("E3").Value = Range("E3").Value - 1


   ActiveWindow.SelectedSheets.PrintOut Copies:=2, Collate:=True, _
        IgnorePrintAreas:=False
Loop




Sheets("ET").Protect Password:="secret"
For Each w In Application.Workbooks
    w.Save
Next w




'
End Sub

 

[Jacko058]

 If Not (Application.Dialogs(xlDialogPrinterSetup).Show) Then Call Macro3
    Exit Sub

Resolved.

Thank you!


Edit:
Nope. Doesn't. Even if a printer is selected the rest of the code doesn't run due to the end sub.
please advise??

 

[Joe4]

If you want to have your IF statements do multiple things, then you cannot do it as one line like this:

    If Not (Application.Dialogs(xlDialogPrinterSetup).Show) Then Exit Sub

If you put the part after "THEN" on the same line as the IF/THEN, then you can only do one command.
If you want multiple, then you need to break it up into multiple lines and add an END IF, like this:

    If Not (Application.Dialogs(xlDialogPrinterSetup).Show) Then 
        Call Macro3
        Exit Sub
    End If