ToggleButtons in a UserForm resetting?

LEG_END

Board Regular
Joined
Jan 8, 2017
Messages
65
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:confused:
 
Code:
Sub UNBLOCK_1()
'
' UNBLOCK_1 Macro
'
'
    ActiveSheet.Shapes.Range(Array("Picture 59")).Select
    Selection.Delete
    Call UNDO_1
    Range("Q25").Select
End Sub

AND

Code:
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...

Code:
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
 
Last edited:
Upvote 0

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
ok thanks but will also need to see the codes you are calling.

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

AND

Code:
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...

Code:
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
 
Upvote 0
Hi,
Give following try & see if any help.

ToggleButton Code:

Rich (BB code):
 Private Sub ToggleButton17_Click()    
  If Me.Visible Then BLOCKUNBLOCK Me.ToggleButton17
End Sub

Place updated code in UserForm_Intialize Event:

Rich (BB code):
     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:

Rich (BB code):
 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
 
Last edited:
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
Hi Dave,

Nailed it...

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

and

Code:
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top