Highlighting Boxes if a column width =0

[Jaywar2013]

Hi All

I have some simple Open/Close text macro buttons which reduce the column width to 0 when pressed, then when another is pressed it sets it to the desired width.

I have been asked if it is possible to highlight something to tell the user which are set to 0 (Highlighted Red) and which are set to the normal width (Highlighted green)

I would be good if it was the label text that changed colour in the text boxes if possible.

Your hopefully

Jay

 

[Jaywar2013]

Awsome Cheers

 

[Jaywar2013]

Hi Rory

I have another poser for this topic if I may?

If I select the button to go red and close the column and then re run my report the column opens however the button remains red and states closed.

Is there a way that when the sheet is activated it makes the button turn Green (Open)


The report when run always keeps opens all the columns.

 

[RoryA]

You can use the Worksheet_activate event but you need to provide the name of the shape:

private sub worksheet_activate()
   With ActiveSheet.Ovals("Oval 1")
      .Font.Color = vbWhite
         .Caption = "Open"
         .Interior.Color = vbGreen
   End With
End Sub

 

[Jaywar2013]

Hi Rory this worked a dream thank you.

However when I see things that are possible I keep getting more questions (You will most probably hate me for it soon)

Private Sub worksheet_activate()


With ActiveSheet.Ovals("Oval 50")
.Caption = "Close Title 1"
.Font.Color = vbBlack
.Interior.Color = vbGreen


End With


With ActiveSheet.Ovals("Oval 51")
.Caption = "Close Title 2"
.Font.Color = vbBlack
.Interior.Color = vbGreen


End With


With ActiveSheet.Ovals("Oval 52")
.Caption = "Close Title 3"
.Font.Color = vbBlack
.Interior.Color = vbGreen


End With


With ActiveSheet.Ovals("Oval 53")
.Caption = "Close Title 4"
.Font.Color = vbBlack
.Interior.Color = vbGreen


End With


With ActiveSheet.Ovals("Oval 54")
.Caption = "Close Title 5"
.Font.Color = vbBlack
.Interior.Color = vbGreen


End With


With ActiveSheet.Ovals("Oval 55")
.Caption = "Close Title 6"
.Font.Color = vbBlack
.Interior.Color = vbGreen
End With
End Sub

now each time I go off the page and back again they are all green and say open , is there a way of clicking on a close button (Which hides the column) going off the page back on the page and it saying closed still?

i.e if the column = 0 (Hidden) then closed/red if the column is greater than 0 then open/green but doing this check by the activation of the sheet?

 

[RoryA]

Yes there is. You already have all the pieces now, so have a go yourself and then post back with what you tried if you get stuck.

 

[Jaywar2013]

Private Sub worksheet_activate()

Columns ("B:B").Hidden = Not Columns ("B:B").Hidden
With ActiveSheet.Ovals("Oval 50")
If Columns("B:B").Hidden Then .Caption = "Open Title 1"
.Interior.Color = vbRed
Else
.Caption = "Close Title 1"
.Font.Color = vbBlack
.Interior.Color = vbGreen


End With

Repeat for other 5 buttons then

End if

End with

??

 

[RoryA]

Pretty much, but remove the

Columns ("B:B").Hidden = Not Columns ("B:B").Hidden

line since that will toggle the column visibility, which you don't want.

 

[Jaywar2013]

Private Sub worksheet_activate()


With ActiveSheet.Ovals("Oval 50")
Columns("B:B").Hidden = Not Columns("B:B").Hidden

.Font.Color = vbWhite
If Columns("B:B").Hidden Then
.Caption = "Open Title 1"
.Interior.Color = vbRed
Else
.Caption = "Close Title 1"
.Font.Color = vbBlack
.Interior.Color = vbGreen




End If

Repeat 5 times (For each button)

End if

End Sub



I cannot get it to work

Its doing all sorts of weird stuff lol

 

[Jaywar2013]

Ok So I now have:

Private Sub worksheet_activate()


  With ActiveSheet.Ovals("Oval 50")
  
   
      .Font.Color = vbWhite
      If Columns("B:B").Hidden Then
         .Caption = "Open 101"
         .Interior.Color = vbRed
      Else
         .Caption = "Close 101"
         .Font.Color = vbBlack
         .Interior.Color = vbGreen




End If


With ActiveSheet.Ovals("Oval 51")


   
      .Font.Color = vbWhite
      If Columns("G:G").Hidden Then
         .Caption = "Open Actions"
         .Interior.Color = vbRed
      Else
         .Caption = "Close Actions"
         .Font.Color = vbBlack
         .Interior.Color = vbGreen


End If






With ActiveSheet.Ovals("Oval 52")
 
   
      .Font.Color = vbWhite
      If Columns("C:C").Hidden Then
         .Caption = "Open Current"
         .Interior.Color = vbRed
      Else
         .Caption = "Close Current"
         .Font.Color = vbBlack
         .Interior.Color = vbGreen
         
   


End If


With ActiveSheet.Ovals("Oval 53")


   
      .Font.Color = vbWhite
      If Columns("D:D").Hidden Then
         .Caption = "Open       102"
         .Interior.Color = vbRed
      Else
         .Caption = "Close       102"
         .Font.Color = vbBlack
         .Interior.Color = vbGreen
         


End If


With ActiveSheet.Ovals("Oval 54")


   
      .Font.Color = vbWhite
      If Columns("E:E").Hidden Then
         .Caption = "Open       "
         .Interior.Color = vbRed
      Else
         .Caption = "Close      "
         .Font.Color = vbBlack
         .Interior.Color = vbGreen


End If


With ActiveSheet.Ovals("Oval 55")


   
      .Font.Color = vbWhite
      If Columns("F:F").Hidden Then
         .Caption = "Open       "
         .Interior.Color = vbRed
      Else
         .Caption = "Close       "
         .Font.Color = vbBlack
         .Interior.Color = vbGreen




End If




End With
End With
End With
End With
End With
End With










End Sub

my problem is that this takes over and when i run the sheet the columns are open but the button stays red

 

[RoryA]

What do you mean by "this takes over"?

BTW, I would change that to:

Private Sub worksheet_activate()

    With Me.Ovals("Oval 50")
        .Font.Color = vbWhite
        If Columns("B:B").Hidden Then
            .Caption = "Open 101"
            .Interior.Color = vbRed
        Else
            .Caption = "Close 101"
            .Font.Color = vbBlack
            .Interior.Color = vbGreen
        End If
    End With

    With Me.Ovals("Oval 51")

        .Font.Color = vbWhite
        If Columns("G:G").Hidden Then
            .Caption = "Open Actions"
            .Interior.Color = vbRed
        Else
            .Caption = "Close Actions"
            .Font.Color = vbBlack
            .Interior.Color = vbGreen
        End If
    End With
    With Me.Ovals("Oval 52")
        .Font.Color = vbWhite
        If Columns("C:C").Hidden Then
            .Caption = "Open Current"
            .Interior.Color = vbRed
        Else
            .Caption = "Close Current"
            .Font.Color = vbBlack
            .Interior.Color = vbGreen
        End If
    End With

    With Me.Ovals("Oval 53")

        .Font.Color = vbWhite
        If Columns("D:D").Hidden Then
            .Caption = "Open 102"
            .Interior.Color = vbRed
        Else
            .Caption = "Close 102"
            .Font.Color = vbBlack
            .Interior.Color = vbGreen
        End If
    End With
    With Me.Ovals("Oval 54")
        .Font.Color = vbWhite
        If Columns("E:E").Hidden Then
            .Caption = "Open "
            .Interior.Color = vbRed
        Else
            .Caption = "Close "
            .Font.Color = vbBlack
            .Interior.Color = vbGreen
        End If

    End With
    With Me.Ovals("Oval 55")
        .Font.Color = vbWhite
        If Columns("F:F").Hidden Then
            .Caption = "Open "
            .Interior.Color = vbRed
        Else
            .Caption = "Close "
            .Font.Color = vbBlack
            .Interior.Color = vbGreen
        End If
    End With

End Sub