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:
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

RedBeard

Well-known Member
Joined
May 16, 2015
Messages
858
Do you have any code in the form/buttons?
If so, share that and we can have a look.
 

LEG_END

Board Regular
Joined
Jan 8, 2017
Messages
65
Hi RedBeard, thanks for your time.

I managed to solve this by slipping in UserForm_QueryClose n the UserForm code as follows:

Code:
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = vbFormControlMenu Then
        Cancel = True
        Me.Hide
    End If
End Sub

Thanks for taking the time to reply its much appreciated. However, just a small bug in that when the actual project is opened initially the toggle buttons do NOT update with the previous SAVED condition of the data... any ideas on this?

Basically if I save my XL and then close it completely I would like my toggle buttons on the userform to update as per the data stored in my worksheet.


The QueryClose keeps my toggle buttons in the desired positions for the duration of the XL Sheet running, I basically am interested in running some code when the userform first opens to check the data and update the toggle buttons respectively.


Thanks in advance:confused:
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
You'll need code in the Initialize event that looks at the stored data and sets the toggle buttons accordingly.
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,252
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

When you use UserForm1.hide it just hides the UserForm and retains the values and settings. When use Unload UserForm1 all the settings and values in the UserForm are lost.
Hi RedBeard, thanks for your time.

I managed to solve this by slipping in UserForm_QueryClose n the UserForm code as follows:

Code:
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = vbFormControlMenu Then
        Cancel = True
        Me.Hide
    End If
End Sub

Thanks for taking the time to reply its much appreciated. However, just a small bug in that when the actual project is opened initially the toggle buttons do NOT update with the previous SAVED condition of the data... any ideas on this?

Basically if I save my XL and then close it completely I would like my toggle buttons on the userform to update as per the data stored in my worksheet.


The QueryClose keeps my toggle buttons in the desired positions for the duration of the XL Sheet running, I basically am interested in running some code when the userform first opens to check the data and update the toggle buttons respectively.


Thanks in advance:confused:
 

LEG_END

Board Regular
Joined
Jan 8, 2017
Messages
65
Thanks for everyones replies so far...

I have the following code applied to my togglebutton (when it is pressed/depressed it basically changes the colour of a line on my sheet to indicate Green - SAFE and Black LIVE:

ToggleButton_Click code:

Code:
[Private Sub ToggleButton1_Click()
If Me.ToggleButton1.Value = True Then
    Call CET1_107H_SAFE
    
Else: Call CET1_107H_LIVE
    
End If
If ToggleButton1.Value = True Then
    ToggleButton1.BackColor = RGB(0, 255, 0)
Else
    ToggleButton1.BackColor = RGB(255, 0, 0)
End If
End Sub
/CODE]


My macro for SAFE - Green Colour

[CODE]Sub CET1_107H_SAFE()
'
' CET1_107H_SAFE Macro
' CET 1 / 107H - SAFE
'
'
    ActiveSheet.Shapes.Range(Array("Straight Connector 3")).Select
    With Selection.ShapeRange.Line
        .Visible = msoTrue
        .ForeColor.RGB = RGB(0, 255, 0)
        .Transparency = 0
    End With
    Range("Q25").Select
End Sub

My Macro for LIVE - Black Colour

Code:
Sub CET1_107H_LIVE()
'
' CET1_107H_LIVE Macro
' CET1 - LIVE
'
'
    ActiveSheet.Shapes.Range(Array("Straight Connector 3")).Select
    With Selection.ShapeRange.Line
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorText1
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = 0
        .Transparency = 0
    End With
    Range("Q25").Select
End Sub

I cant seem to get my code right for userform_initialise for the buttons to update after checking the colour of the lines so that when the userform opens it shows the previous saved colour state of the line?

I hope this makes sense what I am trying to achieve

Thanks Izzy
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Izzy

Earlier you seemed to indicate you were storing the status of the toggle buttons somewhere, eg in cells, but all it seems you are doing is changing the colour of a group of line(s).

Now you could use the lines as 'data' but it would be much easier to store the status of the buttons on a worksheet somewhere and then use that data to set the status of the buttons, and the colour of the lines, when the form opens.
 

LEG_END

Board Regular
Joined
Jan 8, 2017
Messages
65
Norie,

I see your thinking on this however, my code is all but complete with this final step. And to change the way I've done things now would require a major overhaul of the code as I have 12 buttons in total.

The reason for my method is I am using the buttons to change the colours on a much bigger visualisation which indicates when and when not a 25KV line is energised.

If I change my method then I will have a mountain of work in front of me.

I'm very new to VBA so I'm just learning as I go to be honest.

If I cant code it this way then I can go back and change it but if I can get away with it for now I will try!
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
Are you storing any 'data'?

You seemed to indicate you were in post #3.
LEG_END said:
Basically if I save my XL and then close it completely I would like my toggle buttons on the userform to update as per the data stored in my worksheet.

The code you posted for QueryClose is simply hiding the userform.
 

LEG_END

Board Regular
Joined
Jan 8, 2017
Messages
65
"Data" be it a value or the colour of a line is still data in its usable format. Maybe I should have been a little clearer.

I know the query close is simply hiding it but this works for the interim as while the sheet is open and running for 12 hours ata time it works...

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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,518
Messages
5,596,622
Members
414,082
Latest member
sasmita

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
Top