msoButton[Up|Down] as cell format toggle

ozNoz · Jun 11, 2004

There are a number of refs in previous posts about toggle buttons for worksheet states, but I couldn't find any way to have a custom menu button toggle up/down according to the highlighted cell formatting, as many standard buttons do (Bold, Center, borders &c.).
I've got as far as depressing the button when applying certain formats, and raise it when removing, but changing the active cell doesn't update the button state, so moving from a cell I just formatted to one that is not leaves the button (and me) depressed

How does Excel do this? How do I? It would seem overkill to check the cell formatting on each worksheet_change...

Thanks all,
ozNoz (wants toast

)

Greg Truby · Jun 11, 2004

Yep - you're going to have to check on each Selection Change event and update the state of the button (unless Ivan or one of the other API wizards around here know something better).

FWIW - congrats on getting as far as you did - that's a feat in and of itself.

ozNoz · Jun 11, 2004

Greg, I wish I could say I got there myself, but without your posts I'd be stuck with Excel / VBE help, and we all know that's no fun. I'd been through that thread a couple times, and gained much from so doing.
I can't say enough good things about the time and effort all you folks put into this board. I don't doubt that for every new post, there are ten questions that are answered with a quick search and I guess it's rare that people get thanked for those;

ray:

It's Friday and I'm all warm and fuzzy, apparently.

ozNoz · Jun 11, 2004

...bump!

It's been a while, maybe someone has fresh ideas...

Juan Pablo González · Jun 11, 2004

Well, the thing is that Excel doesn't expose any events when the format changes. So the *best* if that can be said, is to use either (or all) the _Change, _SelectionChange, _Calculate events to check the slection's format.

The main issue that this creates is overhead to the workbook, especially if you use the _Calculate event.

Now, question... how are you changing the format or what format are you changing ?

Greg Truby · Jun 11, 2004

Sorry, Oz, so you really want to do this... okay (yuck but here it is...)

You'll need to create a class module (probably in your Personal Macro Workbook) - let's call it clsExcelApp - with a public variable along the lines of:

Public WithEvents MyApp As Application

Also in the class module you'll want to create your needed event handler:

Private Sub MyApp_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
...your code here to find the button and update its state...
End Sub

And then (again, in your Personal Workbook) you need to create an instance of your class and an instance of your App object variable:

In the declarations section in Personal WB code module:

Dim MyXL As New clsExcelApp

And in the Personal WB's open method:

Set MyXL.MyApp = Application

I think that's about right... Like JPG said - overhead here - especially considering we're looking at intercepting the SelectionChange event, which happens all the time...

ozNoz · Jun 11, 2004

Juan, the format is simply adding (or removing) internal diagonal borders in a cell, creating a checkmark effect. Here's what I have so far:

Code:

Sub xCheck(Optional ByVal rngTarget As Range)
'
' adds diagonal in-cell borders (X) to cell
' activated on cell entry of "x", "X" or by custom button
'
    Dim xCheckButton As CommandBarButton
    Dim intLineStyle As Integer
    
    If Not rngTarget Is Nothing Then rngTarget.Select
    If Selection.Column > 3 Then
        Application.ScreenUpdating = False
        ActiveSheet.Unprotect
        Set xCheckButton = Application.CommandBars.ActionControl
        If Not xCheckButton Is Nothing Then _
            xCheckButton.State = IIf(xCheckButton.State = msoButtonDown, msoButtonUp, msoButtonDown)
        intLineStyle = IIf(Selection.Borders(xlDiagonalUp).LineStyle = xlNone, xlContinuous, xlNone)
        With Selection
            .ClearContents
            .Borders(xlDiagonalUp).LineStyle = intLineStyle
            .Borders(xlDiagonalDown).LineStyle = intLineStyle
        End With
        ActiveSheet.Protect
        Application.ScreenUpdating = True
    End If
End Sub

I also have a call in worksheet_change:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Value = "x" Or Target.Value = "X" Then xCheck Target
End Sub

Almost everything works as I wished, except that the toggle button doesn't toggle when changing cells, or (I've just realozed) if the macro fires from Worksheet_Change.

Greg Truby · Jun 11, 2004

Oz,

I think I've misunderstood...

Is this a toolbar button you have added to a toolbar that will always be visible, i.e. on the "Standard" or "Formatting" toolbars? Or is this on a Custom Toolbar that will only be visible when this particular workbook is open? I had thought we were talking the former. If it's the latter, then my last post is not relevent in this context.

ozNoz · Jun 11, 2004

no Greg, you're right: the button sits on my formatting toolbar, and would be handy to have available for all sheets, but I'm starting to think from your last suggestions that it may be more trouble than it's worth. After all, the button works, it just doesn't look very pretty.

Plus, even after all that if anyone else wants to use the same forms they have to go through all that too, I assume? Hmm...
I'll have a look at the class stuff and see how we go, but many thanks already to you and Juan for your input.

Greg Truby · Jun 11, 2004

Oz,

Just thought you might find this interesting. It's from a Customs Form I did a few years back. I needed to duplicate the look of the Customs Form which was a small, graphical square that, when checked, needed to display an "×". Anyway - just for reference...

'==============================================================================
Sub ManipulateBox()
'------------------------------------------------------------------------------
' The various checkboxes are composed of a rectangle object and a group object
' The group is two lines that form the "X".
' Objects can be assigned macros. The various rectangles on the template have
' been assigned a corresponding macro in this module. When AddNew216 copies
' the template worksheet, the objects are copied along with their properties,
' including macro assignments. The copying process generates default object
' names that are different than the names for the same object on the template
' worksheet.
' The various line groups are giving default names that correspond to the
' programming below. The object on the template itself have other names, so
' clicking the boxes there will sometimes fail, sometimes it will cause the "X"
' in a different box to appear/disappear.

 On Error Resume Next
 ActiveSheet.Shapes("Group 13").Line.Visible = Not ActiveSheet.Shapes("Group 13").Line.Visible
End Sub

'==============================================================================
Sub ManufactureBox()
 On Error Resume Next
 ActiveSheet.Shapes("Group 10").Line.Visible = Not ActiveSheet.Shapes("Group 10").Line.Visible
End Sub

'==============================================================================
Sub ExhibitBox()
 On Error Resume Next
 ActiveSheet.Shapes("Group 7").Line.Visible = Not ActiveSheet.Shapes("Group 7").Line.Visible
End Sub

'==============================================================================
Sub DestroyBox()
 On Error Resume Next
 ActiveSheet.Shapes("Group 4").Line.Visible = Not ActiveSheet.Shapes("Group 4").Line.Visible
End Sub

msoButton[Up|Down] as cell format toggle

ozNoz

Board Regular

Excel Facts

Greg Truby

MrExcel MVP

ozNoz

Board Regular

ozNoz

Board Regular

Juan Pablo González

MrExcel MVP

Greg Truby

MrExcel MVP

ozNoz

Board Regular

Greg Truby

MrExcel MVP

ozNoz

Board Regular

Greg Truby

MrExcel MVP

Similar threads

Forum statistics

Share this page

msoButton[Up|Down] as cell format toggle

Board Regular

Excel Facts

MrExcel MVP

Board Regular

Board Regular

MrExcel MVP

MrExcel MVP

Board Regular

MrExcel MVP

Board Regular

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock