Simple Checkbox Programming 2

[PetroAg]

Howdy,
Basically I want to know how to click on the box and make it output a true or false value so that i can use it to choose which equations i need to use.

An example psudocode might be...

Is the box checked?
Yes: The Well is deviated
No: The Well is vertical
if deviated run the deviation equations
else run the vertical equations.

I can do the "if true" part but I dont know how to do the

Private Sub Checkbox_???? ()

end Sub

Or How to call it.

Can anyone help me please?

Thank you

 

[joerd]

Easiest is to create the checkbox with the ControlToolbox (which is a toolbar, just like the Forms toolbar).
Then, while in design mode, right-click the new checkbox (it is selected when you create it), and click on View Code.
You can choose between several events (top right), such as Click, or Change, or...
In the event procedure, write something like:

Private Sub CheckBox1_Click()
[a1] = Me.CheckBox1.Value
End Sub

Me refers to the sheet that the checkbox is on.
Me.CheckBox1 is your checkbox - if you decide to give it another name, then this should of course be changed.
.Value gives you either TRUE or FALSE (or Null, in case of a triple-state (greyed) checkbox), dependent on the status of the checkbox.


Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[Mutt]

If your formulae are in the worksheet cells instead of in Vbasic you can use a checkbox from the Form toolbar, right click to format control and link to a cell in the sheet. In the equations refer to the cell which will now contain true or false. I usually refer to a cell immediately underneath the checkbox and then 'fill' the checkbox with white so can't see thru it!

Of course a macro could also pick up the linked cell value in usual manner when executed. Clicking the checkbox will not then initiate the macro - useful when having lots boxes to be checked/unchecked by a user before executing the macro from a button.

By the way : In Excel 2000 I found in several cases that when the spreadsheet got large, in excess of 3MB with load of formulae not data, that adding one checkbox too many caused the workbook to be corrupted and could not be opened to that sheet - if saved while on that sheet, can't open the workbook! Have since decided to get the user to use false/true values in cells with data validation to select methods etc. Perhaps was just me......

 

[sullivandavid]

There are several ways to do this
since this boolean you could simply
do this
select a cell create a table Yes No
Using data validation select list in the pulldown
reference the table

or use form toolbar use radio button not check boxes
as your choice is yes or no with this otherwise you can have both equations running
then right each radio button and assign your code

I wrote a sample program if you like and can send it to you if care called Well

 

[sullivandavid]

My sample has yes/no radio button changes color of the cell
RED or Green creates a message box as to what equations are running

I have created two named ranges Well and Well Status

and radio box will reset status to unknown (o) no checks when opened

Private Sub Workbook_Open()
Application.Run "Well!Well_Status_Unknown"
End Sub

Sub Well_Status_Unknown()
'
'
Range("Well_Status&quot.Select
'
Range("well_status&quot = "Well Status Unknown"
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5
End With
Range("Well&quot = 0
End Sub

Sub Well_deviated()
'
' Well_deviated Macro
'
MsgBox "Now Running Deviation Equation"
Range("Well_Status&quot.Select
Range("Well_Status&quot = "The Well is deviated"
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
Selection.Font.ColorIndex = 2
'insert your code



End Sub
Sub Well_Vertical()
'
' Well_vertical Macro

MsgBox "Now Running Deviation Equation"
Range("Well_Status&quot.Select
Range("Well_Status&quot = "The Well is Vertical"
Selection.Interior.ColorIndex = 50 'green

Selection.Font.Bold = True
Selection.Font.ColorIndex = 2
'insert your code
End Sub