1.)Input Boxes 2.) Condensing a Code

[David W]

1.)Is there a way to stop a particular part of the code from continuing once
someone selects cancel while in an input box. I have got this code below
and when you select cancel it keeps going with the code instead of stopping
and moving to the next section of code.

2.)Is there a way to condense this code so it will fit a scope of 160 rows
being that Excel and my pc don't like 1,200 checkboxes with code

Thanks ahead if you reply to this

Private Sub CheckBox9_change()
Dim wksh As Worksheet
Dim res As Variant
Me.Unprotect ("?")
If CheckBox9.Value = True Then
Set b = Assistant.NewBalloon
With b
.Heading = "Fuel Type"
.Text = "Please Select A Fuel Type To Use With This Vehicle:"
.Labels(1).Text = "Gasoline"
.Labels(2).Text = "On Road Fuel"
.Labels(3).Text = "Off Road Fuel"
.Labels(4).Text = "None"
returnvalue = .Show
Range("k30").Value = returnvalue

If Range("k30") = 1 Then

For i = 1 To 12
sname = Choose(i, "jan", "feb", "march", "april", "may", _
"june", "july", "aug", "sept", "oct", "nov", "dec")

Set wksh = Worksheets(sname)
wksh.Unprotect ("?")
wksh.Range("b11").Locked = Not Me.CheckBox9.Value
wksh.Protect ("?")
Next
End If
End With
res = Application.InputBox("Enter Silences Plate Number:", Type:=2)
If res <> False Then
Range("j30").Value = res
Else
MsgBox "You clicked cancel"
End If
Else
Range("j30").Value = ""
Range("k30") = "4"
End If
If Me.CheckBox9.Value = True Then
Else
MsgBox "Deactivating Vehicle"
End If
Me.Protect ("?")

For i = 1 To 12
sname = Choose(i, "jan", "feb", "march", "april", "may", _
"june", "july", "aug", "sept", "oct", "nov", "dec")
Set wksh = Worksheets(sname)
wksh.Unprotect ("?")
wksh.Range("e11:F11,h11:k11").Locked = Not Me.CheckBox9.Value
wksh.Protect ("?")
Next
Me.Unprotect ("?")

If CheckBox9.Value = True Then
res = Application.InputBox("Enter Fuel Card Number:", Type:=1)
If res <> False Then
Range("d30").Value = res
Else
MsgBox "You clicked cancel"
End If
Else
Range("d30").Value = ""
End If
If Me.CheckBox9.Value = True Then
Else
MsgBox "Deactivating Fuel Card"
End If
Me.Protect ("?")
For i = 1 To 12
sname = Choose(i, "jan", "feb", "march", "april", "may", _
"june", "july", "aug", "sept", "oct", "nov", "dec")

Set wksh = Worksheets(sname)
wksh.Unprotect ("?")
wksh.Range("o1111").Locked = Not Me.CheckBox9.Value
wksh.Protect ("?")
Next


End Sub

 

[Thomas]

If you want to force them into entering something in the input box no
matter what
Do Until res <> False
res = Application.InputBox("Enter Fuel Card Number:", Type:=1)
If res <> False Then
Range("d30").Value = res
Else
MsgBox "You clicked cancel"
End If
Loop

 

[David W]

Harald,
Thanks for responding
Exit sub will do, I didnt think about that, sometimes the most simplest
things you overlook, I am still learning vba,as you guessed, I started a few
months ago writing code and I dont know it all, but I am learning thanks to
people like you.
As far as for the abundance of code, I know its alot, but when you try to
make it fool proof for the end user(not me)you have to do things that you
ordinarly wouldnt do.
Do you know how and/or do you think it would help if I used the
(unload)from memory after running this code until a checkbox is selected and
(load)into memory would help. This code is on 1 sheet that will hardly ever
be changed throughout the year, it is a setup sheet for the rest of the
sheets that have various cells that you tab through and enter data into.