Question on ToggleBox / CheckBox objects knowing where they exist in a sheet

[Sean O'Neill]

Try and describe what I have in simple terms.

I have say a column with "X" in various rows indicating a
specific state (e.g. turned on, selected, etc) for that row.

What I would like to do it put something at the top of the
column that I can check, mark, something that runs a Macro
to finds all the "X" and changes them to something else -
say an "F" for failed. Then when I uncheck, unmark,
whatever this object, it finds all the "F" and changes them
back to "X".

I was playing with the Togglebox last night but it doesn't
know its location in a worksheet.

Do cells have like on-mark, on-click or something that I
could use instead which I think could work.

I basically need to be able to mark something. The macro
can then use the cell/column address of that object to know
that its needs to search that column for "X" and change to
"F" and back on unclick, mark, whatever.

Any thoughts ?

 

[Dave Peterson]

It sounds like you have more than one column to do this to.

If yes, then put a button from the Forms toolbar in the topcell of each column.

Assign each the same macro:

Option Explicit
Sub toggleFX()

Dim myBTN As Button
Dim myFrom As String
Dim myTo As String

Set myBTN = ActiveSheet.Buttons(Application.Caller)

With myBTN
If LCase(.Text) = "x->f" Then
myFrom = "x"
myTo = "f"
.Text = "F->X"
Else
myFrom = "f"
myTo = "x"
.Text = "X->F"
End If
With .TopLeftCell.EntireColumn
.Cells.Replace what:=myFrom, replacement:=myTo, _
lookat:=xlWhole, MatchCase:=False
End With
End With

End Sub

Make sure you put the correct starting text in the button (or you may have to
click twice for the first one to work correctly).

If you're new to macros, you may want to read David's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Sean O'Neill]

It sounds like you have more than one column to do this to.

Yes, my worksheets are *LOTS* of columns that I would like to do this
with.

If yes, then put a button from the Forms toolbar in the topcell of each
column.

<SNIP>

I may tweak the code to ignore case so either "x" or "X" will work.

Make sure you put the correct starting text in the button (or you may have to
click twice for the first one to work correctly).

If you're new to macros, you may want to read David's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Years ago I taught myself VBA programming in Excel but I haven't touched
it in years and I'm feeling the pain.

Thanks for the reply

 

[Dave Peterson]

The tweaking of the code may look a lot like: MatchCase:=False in your .find
statement

 

[Sean O'Neill]

The tweaking of the code may look a lot like: MatchCase:=False in your .find
statement

Yep - that's exactly what it was ... and already in the example you sent


BTW, I learned painfully to read specifically "forms toolbar" vs. command
toolbar. I tried using naively the command toolbar and I couldn't get
anything to work even after readying your post 2 and 3 times.

FINALLY I saw read it once more and a *thunder* bolt hit me and everything
worked perfectly

 

[Dave Peterson]

The forms toolbar stuff is older, but has the nice feature that you can assign
the same macro to each--just with assign macro.

And they're sometimes easier to work with.

Glad you got it working.