Checkbox Code

[dparteka]

I think I’m real close here but there is a problem I haven’t been able to figure out. What should happen is when I check the checkbox the macro should look in A1, if nothing is there it should simply shutdown and change none of the checkbox properties. When a date is entered in A1 and the checkbox is clicked it should run the full macro.

If I run only the top portion of the macro it works fine and if I run only the bottom portion of the macro it also works fine… when I run them together it shuts down with an error.

File is attached... any help would be greatly appreciated… thanks Dennis

Private Sub CheckBox2_Click()

On Error GoTo EXITSUB:
 Me.CheckBox2.Value = IIf(Range("A1").Value = "", False, True)
EXITSUB:

Set objShell = CreateObject("WScript.Shell")
Set objFSO = CreateObject("Scripting.FileSystemObject")
objFSO.DeleteFile ("H:\Public\PCB-QCR's\" & Range("A2").Text & " QCR.lnk")
ActiveSheet.Shapes("CheckBox2").Select
CheckBox2.Enabled = False
End Sub

 

[Luke M]

Not sure why a checkbox rather than just a button, but what you described would be written as

Private Sub CheckBox2_Click()
If Range("A1").Value = "" Then
    'do nothing
Else
    Kill "H:\Public\PCB-QCR's\" & Range("A2").Text & " QCR.lnk"
    Me.CheckBox2.Enabled = False
End If
End Sub

 

[Luke M]

Also, as a word of caution, this is dangerous:

On Error GoTo EXITSUB:
 Me.CheckBox2.Value = IIf(Range("A1").Value = "", False, True)
EXITSUB: '<---- DANGER
Set objShell = CreateObject("WScript.Shell")
Set objFSO = CreateObject("Scripting.FileSystemObject")
objFSO.DeleteFile ("H:\Public\PCB-QCR's\" & Range("A2").Text & " QCR.lnk")
ActiveSheet.Shapes("CheckBox2").Select
CheckBox2.Enabled = False

by never resetting what the On Error does, if an error occurs in any of the lines after EXITSUB:, you'll get stuck in an infinite loop, as it will error, go up to EXITSUB, and then error again and again. Just a tip.

 

[dparteka]

Luke M... I will heed your caution, thanks for pointing that out, that code was something I found on another web site.

As for your code, I can see I made this a lot more complicated then what was necessary; your code is much cleaner and works perfectly except for one small issue.

In your comments above you stated “not sure why a checkbox rather than just a button”... the reason for the checkbox is so everyone can see that the macro was ignited and the file was deleted. I have added CheckBox2.Value=False to resolve that.

Question: the top portion of the macro works perfectly, I would like to add an error message box to the bottom portion of the macro for when the file does not exist. As you can see in the code I’ve provided here again my lack of experience is preventing me from getting this done, can you suggest an appropriate replacement code?

NOTE: The message box should only be displayed if the file does not exist, any other errors the message would not pop-up.

Best regards & thank you for the help... Dennis

Private Sub CheckBox2_Click()

If Range("A1").Value = "" Then
  'do nothing
  CheckBox2.Value = False


Else
On Error GoTo ErrMsg
  Kill "H:\Public\PCB-QCR's\" & Range("A2").Text & " QCR.lnk"
  Me.CheckBox2.Enabled = False
End If
  
ErrMsg:
MsgBox ("File does not exist"), , "FILE DELETION ERROR"
CheckBox2.Value = True
Me.CheckBox2.Enabled = False

End Sub

 

[Luke M]

Almost got it, just new a few tweaks.

Private Sub CheckBox2_Click()

If Range("A1").Value = "" Then
  'do nothing
CheckBox2.Value = False


Else
  On Error GoTo ErrMsg
  Kill "H:\Public\PCB-QCR's\" & Range("A2").Text & " QCR.lnk"
  'Reset the Error
  On Error GoTo 0
  Me.CheckBox2.Enabled = False
End If
'This would be the normal end of sub
Exit Sub

'Only way now to get down here is if error during the Kill command
ErrMsg:
MsgBox ("File does not exist"), , "FILE DELETION ERROR"
CheckBox2.Value = True
Me.CheckBox2.Enabled = False

End Sub

 

[dparteka]

Luke M... works perfectly, your comments explaining the code is very helpful to my VB evaluation, another piece of the puzzle in place... thanks again