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

 

[LEG_END]

Sub UNBLOCK_1()
'
' UNBLOCK_1 Macro
'
'
    ActiveSheet.Shapes.Range(Array("Picture 59")).Select
    Selection.Delete
    Call UNDO_1
    Range("Q25").Select
End Sub

AND

Sub BLOCK_1()
'
' BLOCK_1 Macro
' BLOCK_1
'
'
    ActiveSheet.Pictures.Insert( _
        "C:\Users\imust\Desktop\Doncaster Berthing Board\Stop sign.jpg").Select
        Selection.ShapeRange.Name = "Picture 59"
    Selection.Name = "Picture 59"
    Selection.ShapeRange.IncrementLeft 272
    Selection.ShapeRange.IncrementTop 19
    Call TRIAL_1
    Range("Q25").Select
End Sub

And trial is...

Sub TRIAL_1()
'
' TRIAL_1 Macro
'
'
    Application.ScreenUpdating = False
    Sheets("Lists").Visible = True
    Sheets("Lists").Select
    Range("B161").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("B162").Select
    Sheets("Berthing_Board").Select
    Range("Q25").Select
    Sheets("Lists").Visible = False
End Sub

 

[dmt32]

ok thanks but will also need to see the codes you are calling.

Dave

 

[LEG_END]

Sub UNBLOCK_1()
'
' UNBLOCK_1 Macro
'
'
    ActiveSheet.Shapes.Range(Array("Picture 59")).Select
    Selection.Delete
    Call UNDO_1
    Range("Q25").Select
End Sub

AND

Sub BLOCK_1()
'
' BLOCK_1 Macro
' BLOCK_1
'
'
    ActiveSheet.Pictures.Insert( _
        "C:\Users\imust\Desktop\Doncaster Berthing Board\Stop sign.jpg").Select
        Selection.ShapeRange.Name = "Picture 59"
    Selection.Name = "Picture 59"
    Selection.ShapeRange.IncrementLeft 272
    Selection.ShapeRange.IncrementTop 19
    Call TRIAL_1
    Range("Q25").Select
End Sub

And trial is...

Sub TRIAL_1()
'
' TRIAL_1 Macro
'
'
    Application.ScreenUpdating = False
    Sheets("Lists").Visible = True
    Sheets("Lists").Select
    Range("B161").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("B162").Select
    Sheets("Berthing_Board").Select
    Range("Q25").Select
    Sheets("Lists").Visible = False
End Sub

Attached above pal

 

[dmt32]

Hi,
Give following try & see if any help.

ToggleButton Code:

 Private Sub ToggleButton17_Click()    
  If Me.Visible Then BLOCKUNBLOCK Me.ToggleButton17
End Sub

Place updated code in UserForm_Intialize Event:

     Dim shp As Shape    
    For Each shp In Worksheets("Sheet1").Shapes
        With Me.ToggleButton17
            .Value = CBool(shp.Name = "Picture 59")
            .BackColor = IIf(.Value, RGB(255, 0, 0), RGB(0, 255, 0))
            If .Value Then Exit For
        End With
    Next shp

Place following code either in your Forms Code Page or standard module:

 Sub BLOCKUNBLOCK(ByVal Toggle As Object)

    Dim Pic As Object
    Dim ws As Worksheet
    Dim FileName As String
    Dim shp As Shape
    Const PictureName As String = "Picture 59"
    
    FileName = "C:\Users\imust\Desktop\Doncaster Berthing Board\Stop sign.jpg"
    
    Set ws = Worksheets("Sheet1")


     If Toggle.Value Then
     
        Toggle.BackColor = RGB(255, 0, 0)
        
    Set Pic = ws.Pictures.Insert(FileName)
    
    With Pic
        .Name = PictureName
        .ShapeRange.IncrementLeft 272
        .ShapeRange.IncrementTop 19
    End With
    
    Call TRIAL_1
    
    Else
    
        Toggle.BackColor = RGB(0, 255, 0)
    
    For Each shp In ws.Shapes
        If shp.Name = PictureName Then shp.Delete: Exit For
    Next shp
    
    End If
End Sub

You will need to amend the worksheet name shown in RED as required.

Solution not fully tested but hopefully goes in right direction for you.

Hope Helpful

Dave

 

[LEG_END]

Thanks again Dave for your efforts its much appreciated.

Unfortunately it comes with the same outcome. My image remains when the worksheet is reloaded and the respective toggle released.

I did read somewhere that excel can weirdly change the property of a shape or picture to its opposite... I wonder if this is the case when it saves and re-opens?

 

[dmt32]

Thanks again Dave for your efforts its much appreciated.

Unfortunately it comes with the same outcome. My image remains when the worksheet is reloaded and the respective toggle released.

I did read somewhere that excel can weirdly change the property of a shape or picture to its opposite... I wonder if this is the case when it saves and re-opens?

Hi,
Little testing I did image on the sheet was deleted when form loaded & togglebutton released.

If you are able to place copy of your workbook in a dropbox happy to have a look although won't be until Monday at earliest.

Dave

 

[LEG_END]

Hi Dave,

Nailed it...

ActiveSheet.Shapes("59").Visible = True

and

ActiveSheet.Shapes("59").Visible = False

I replaced my BLOCK / UNLOCK Module macros for the above...

and instead of putting the image in I just change the visibility, not my first intention however it works so I'll take that.


I'd like to thak you sincerely for your help on this its genuinely much appreciated

 

[dmt32]

Hi Dave,

Nailed it...



I'd like to thak you sincerely for your help on this its genuinely much appreciated

Always more rewarding when resolve yourself - glad to have been of some assistance.

Many thanks for your feedback

Dave