Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
1.)Input Boxes 2.) Condensing a Code
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("o11:p11").Locked = Not Me.CheckBox9.Value wksh.Protect ("?") Next End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
1.)Input Boxes 2.) Condensing a Code
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Condensing VBA code | Excel Discussion (Misc queries) | |||
Interactive Data Input Boxes | Excel Discussion (Misc queries) | |||
Input boxes | Excel Discussion (Misc queries) | |||
drop boxes, entering input | Excel Discussion (Misc queries) | |||
How do I add input data in the input ranges in drop down boxes. | Excel Discussion (Misc queries) |