ToggleButtons in a UserForm resetting?

[LEG_END]

Hi,

I am having issues that when my userform activates each time my toggle buttons x12 of are all back in their default position.

I'm still learning VBA so a little wet behind the ears.

Is there a way that when my userform is activated it looks at the values of my data and returns to the position that corresponds with the data?

thanks

Izzy

 

[dmt32]

What I'd like to do is get it to work from start up... I know where I should put it I'm just not sure how to get it to look up the colour of the lines, I've tried if/else but just can't seem to get my statements right and that's what I was looking to get from here

Hi,
If all you want to do is check color of the 12 line shapes & set togglebuttons accordingly, then solution to your requirement should be quite straight forward if you can change the default indexes in the names of your shapes to match that of each of your ToggleButtons.

For instance if ToggleButton1 sets "Straight Connector 3" color then it will need to be renamed "Straight Connector 1" and so on for the all others.

The for your forms Initialize event

Private Sub UserForm_Initialize()
    For i = 1 To 12
    Me.Controls("ToggleButton" & i).Value = _
    Not CBool(Sheets("Sheet1").Shapes("Straight Connector " & i).Line.ForeColor.RGB = 255)
    Next i
End Sub

What this should do is set each togglebutton (name index) according to the line (name index) color (RED False, Green True)

You will need to change the sheet name shown in RED as required.

Hope Helpful

Dave.

 

[LEG_END]

thanks Dave this was a great help. Pointed me in the right direction thank you.

However I now have a similar issue but this time I want to check the page to see if a imge is present, if so again I wish to have my toggle buttons appear in the correct position...

for example if my push button is TRUE I paste an image to the screen, if its FALSE I remove it...

what I want to do is code in the initialise to scan for the image name in thesheet, if its present, then I want my toggle to mactch the status of the image being present or not

thope this makes sense

 

[dmt32]

thanks Dave this was a great help. Pointed me in the right direction thank you.

However I now have a similar issue but this time I want to check the page to see if a imge is present, if so again I wish to have my toggle buttons appear in the correct position...

for example if my push button is TRUE I paste an image to the screen, if its FALSE I remove it...

what I want to do is code in the initialise to scan for the image name in thesheet, if its present, then I want my toggle to mactch the status of the image being present or not

thope this makes sense

Hi,
Glad solution helped you I note I got the colours you were using mixed up but in my defence, it was past my bedtime.

What you are asking is a little too vague to offer a workable solution but as a suggestion, you could maybe, create an array of the names and use Application.Match to check for their existence & toggle your buttons based on result.

If you are able to place copy of your workbook in a dropbox it would be very helpful.

Dave

 

[LEG_END]

Thanks again Dave for the reply...

Basically I have an image that is displayed on the spreadsheet when a toggle is TRUE. And is removed when FALSE.

The image is defined as "Picture 59"

When I save the spreadsheet at the end of the day then re-open it, I want to have the toggle button update to the last saved position, i.e. if the picture is present then the Toggle will be TRUE and Vise versa...

If Application.Match("Picture 59") Then
    Me.ToggleButton17.Value = True
    
Else: Me.ToggleButton17.Value = False
End If

Does this explain what I am trying to achieve?

 

[dmt32]

In that case maybe this will do what you want

Place code in the UserForm_Initialize event

     Dim shp As Shape    
    For Each shp In Worksheets("Sheet1").Shapes
        Me.ToggleButton17.Value = CBool(shp.Name = "Picture 59")
    Next shp

change sheet name as required.

Dave

 

[LEG_END]

Thanks pal, I tried this but it came up with a subscript error...

I have found a way be it a long way round but it works... I wrote in to the code when the image is inserted I basically populate a small table in a hidden sheet.

'DC1
If ws.Range("B161") = "1" Then
    Me.ToggleButton17.Value = True
    ToggleButton17.BackColor = RGB(255, 0, 0)
Else: Me.ToggleButton17.Value = False
    ToggleButton17.BackColor = RGB(0, 255, 0)
End If

ws = worksheet "Lists"

This however has posed its own problem, this updates as it should. But now say I save my spreadsheet with the image attached, when I re-open it the image is displayed and my userfor toggle button reflects this perfectly. When I de-select the toggle it doesn't remove the image, I can manually delete this but should I not manualy delete when the toggle is cycled it now pastes an image over the top of the one that was originally saved.

Izzy

 

[dmt32]

Thanks pal, I tried this but it came up with a subscript error...

Izzy

did you rename the reference to the worksheet as mentioned?

Dave

 

[LEG_END]

haha... good shout... apologies, I'm trying to write this at the same time as running a train depot!!! so apologies.

This worked but also gave me the same outcome when the sheet is saved with the image inserted. When I exit then re-open the image can be seen but when I set the toggle to false the image remains.

Izzy

 

[dmt32]

haha... good shout... apologies, I'm trying to write this at the same time as running a train depot!!! so apologies.

This worked but also gave me the same outcome when the sheet is saved with the image inserted. When I exit then re-open the image can be seen but when I set the toggle to false the image remains.

Izzy

Can you share All the code using behind the ToggleButton.

Dave

 

[LEG_END]

Dave,

Please find attached...

Private Sub ToggleButton17_Click()
If Me.ToggleButton17.Value = True Then
    Call BLOCK_1
    
Else: Call UNBLOCK_1
    
End If
If ToggleButton17.Value = True Then
    ToggleButton17.BackColor = RGB(255, 0, 0)
Else
    ToggleButton17.BackColor = RGB(0, 255, 0)
End If
End Sub