Tick or Toggle

[mlv]

I need to put a manual toggle (or tick box, check box, radio button,
whatever) in each cell of a column so that if the toggle is on/ticked (value
1), a formula extracts the value from an adjacent cell, performs a
mathematical function on it and enters the result in a third cell.

i.e :

Cell A1 contains the toggle (or tick box/check box/radio button)
Cell B1 contains a numerical value
Cell C1 contains the formula.

If Cell A1 toggle is on/ticked, the formula in Cell C1 extracts the value
from Cell B1, performs a mathematical function on it and enters the result
in Cell C1, otherwise (if the toggle is off/unticked), Cell C1 remains blank
("").

This process is repeated for Cell groups A2, B2, C2 & A3, B3, C3, etc.

I would like to use a simple toggle that the user can click-on successively
to alternately change it's state between 0 & 1, rather than requiring the
user to type in something such as 'Y' or 'X' into Cell A1.

Does Excel offer such a feature?

 

[Bob Phillips]

Here is some event code that will put/remove a tick mark in column A, and
then use a formula like so to pick up that value

=IF(A16="a",B16+22,"")

The tick mark is entered/removed by selecting a cell in column A.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const WS_RANGE As String = "A:A" '<== change to suit

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Value = "" Then
.Value = "a"
.Font.Name = "Marlett"
Else
.Value = ""
End If
.Offset(0, 1).Select
End With
End If

End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[mlv]

Here is some event code that will put/remove a tick
mark in column A, and then use a formula like so to
pick up that value

=IF(A1="a",B1+22,"") Formula in Cell C1

The tick mark is entered/removed by selecting a cell in column A.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const WS_RANGE As String = "A1:A12" '<== change to suit

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Value = "" Then
.Value = "a"
.Font.Name = "Marlett"
Else
.Value = ""
End If
.Offset(0, 1).Select
End With
End If

End Sub

Thanks Bob, the routine seems to be working fine, except I keep getting a
"Run-time error '13': Type mismatch".

Debug highlights the line: If .Value = "" Then

I've constrained the tickbox column to A1:A12 (see your edited routine
above)

I'm still working on the design of the Excel sheet and I get the Run-time
error if I select more than one of the tickbox cells at the same time, or
if I select a tickbox cell along with any other cell - e.g. I selected cells
A1:C12 to format a border and that brought up the Run-time error. So did
accidentally picking up Cell A13 (which isn't a tickbox cell) along with
Cell A12 (which is the last tickbox cell in the column).

Simply clicking on 'A' to highlight the whole column (in order to change the
column width) also brings up the Run-time error.

I guess the problem is incompatible data types within the cells selected.
Is there anything I can do to stop this happening?

Thanks

 

[Bob Phillips]

Mike,

The easiest way is to ignore it when you select multiple cells.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const WS_RANGE As String = "A:A" '<== change to suit

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Count = 1 Then
If .Value = "" Then
.Value = "a"
.Font.Name = "Marlett"
Else
.Value = ""
End If
.Offset(0, 1).Select
End If
End With
End If

End Sub

Downside is if you select 3 cells in A1:A12, they won't all get ticked, you
have to selectr them all individually. Let me know if this is a problem.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[mlv]

The easiest way is to ignore it when you select multiple cells.

Not easy to ignore because the error throws up a dialog box with three
button options. If the user picks 'Debug' rather than 'End' they end up
being invited to edit the formula and it's not straightforward to get out of
that situation.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const WS_RANGE As String = "A:A" '<== change to suit

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Count = 1 Then
If .Value = "" Then
.Value = "a"
.Font.Name = "Marlett"
Else
.Value = ""
End If
.Offset(0, 1).Select
End If
End With
End If

End Sub

Thanks, I'll give that a try.

Downside is if you select 3 cells in A1:A12, they won't
all get ticked, you have to select them all individually.
Let me know if this is a problem.

No problem at all. It's better that the user thinks about each row
individually and ticks or unticks the boxes one at a time, as necessary.

BTW, picking more than one tickbox cell at a time with the previous routine
also throws up the "Run-time error '13': Type mismatch". It doesn't tick
(or untick) the group of boxes selected. In fact, virtually anything other
than a single tickbox being picked throws up the "Run-time error '13': Type
mismatch".

I thought I would be clever and format the Cells A1:A12 to use the 'Marlett'
font as the default. That didn't work because when the routine got to the
line '.Font.Name = "Marlett" ' it threw up a "Run-time error '1004': Unable
to set the Name property of the font class". I guess the options are to set
another font as the default and let the routine set the 'Marlett' font, or
set the 'Marlett' font as the default for the tickbox cells and disable the
..Font.Name line in the routine. I would have thought VB could have sorted
that out for itself without throwing up error messages.

Thanks for you help.

 

[Bob Phillips]

I think you misunderstand me and the error <g>

I didn't mean ignore the error when it happend, but the easiest solution is
to ignore multi-selects, do nothing in that case, which is what my revised
code did.

VBA doesn't sort anything out, it does what you tell it to. You know the
prime computer axiom - GIGO. If more than one cell can be selected, the code
has to manage it.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[mlv]

I think you misunderstand me and the error <g>

Yep, I surely did

I didn't mean ignore the error when it happened, but the easiest solution
is to ignore multi-selects, do nothing in that case, which
is what my revised code did.

The revised code works fine, thanks.

VBA doesn't sort anything out, it does what you tell it to. You know
the prime computer axiom - GIGO. If more than one cell can be
selected, the code has to manage it.

Sure. I'm not familiar with VBA (although I'm working on it). I was just
surprised that VBA threw up an error message when asked to set a font that
was already set as the default, as the outcome was still correct, i.e. the
Marlett font was used. This suggests that even more lines of code are
needed - check to see what font is set as the default, then, if it's not the
one you want, set the one you want, otherwise do nothing.

Thanks for your help.