Toggle Button on Workbook Open - value is True but button not pressed?

[excelnat]

Hi all,

I have a toggle button (named 'toggle') on a worksheet to turn a function on and off (this all works fine). However, I need to always have the toggle button 'on' when the workbook is opened, and it is currently not working correctly.

I have 'Sheets("Sheet 1").toggle.Value = True' (not text True) in a Workbook_Open() event, and this is setting the linked cell to True on the workbook opening.

However, the toggle button isn't 'depressing' to show the user the function is turned on?? But the function is still on and working fine??

So I need to know how to make the button turn on, but also depress to show it's 'clicked on'. Can anyone help please?

In addition, is there anyway to change the words on the toggle button to 'On' or 'Off', depending whether the button is pressed down or not? This is just a secondary question, would be great if anyone has an answer to this but it's not important :-)

Thanks all,
Nat :-)

[Norie]

Where did you put the workbook Open code?

Is there any other code that could affect the togglebutton or linked cell?

[excelnat]

Where did you put the workbook Open code?

Is there any other code that could affect the togglebutton or linked cell?

Thanks for your quick reply Norie :-)

The Workbook Open code is in the ThisWorkbook code.

No, I had a VBA If statement dependent upon the Linked Cell (K1), but I changed this already, so that when pressed, the linked cell changes through TRUE/FALSE and now another cell (L1) changes to "On"/"Off" and the If statement now depends on that cell. So now, it's only the Workbook open code that affects the toggle button and nothing else is dependant/linked to the linked cell

NAt

[Norie]

Can you post the exact code you are using in the workbook's Open event?

I tried it with this and it seemed to work fine.

Please Login or Register  to view this content.

[excelnat]

Hi, yeah it is as follows (I hope I've done this right!):

Note that following your above post, I did originally have 'toggle.Value = True', I have now changed this to 'toggle = True' and it still doesn't depress the button!

Please Login or Register  to view this content.

[Norie]

That code looks fine.

When you run it manually (F5) is the toggle button depressed?

By the way, you should use Value, I was just being lazy/forgetful.

[excelnat]

I've not tried that before (didn't know how haha) but yes! It DOES depress the button!

The code runs, the welcome message appears and the button then presses down. then, when I open the file, the welcome message pops up and the button stays up

I really appreciate your help btw, thank you!! :-)

[excelnat]

Also, I don't know if it will help, but I'm using the codes from http://excelribbon.tips.net/T008192_...nactivity.html for the timer function and I wanted to add in the toggle button to turn it off or on...

Nat :-)

[Norie]

I wouldn't think the code for the timer would affect setting the toggle button.

Can you upload an example workbook?

Click on GO ADVANCED and use the paperclip icon to open the upload window.

[excelnat]

I would as I want to get this sorted, however the file contains Data Protected information and it would take me an age to strip the workbook of all sensitive details. Is there a quick/safe way to upload the file or parts of it? Sorry, I've not done this before! :-)

Nat :-)

[Norie]

Can you not make a copy and delete all the data but leave the toggle button and code?

[excelnat]

Argh! Now I've stripped all the data out of the Doc the button is working fine! On opening, I get the welcome message and then the button presses on straight away... I've attached the sample doc.


However... by mistake, I've just noticed that the button IS actually pressing after opening, but it is taking 16 seconds to press down after I click OK on the welcome message. I'm guessing that some code must be slowing it down in that case?

[Norie]

Perhaps the timer code is causing the problem

Try shifting the call to that code after everything else.

Please Login or Register  to view this content.

[excelnat]

:-( No, that hasn't solved it :-(

I think it will just have to stay as it is unfortunately :-( At least it serves its purpose :-)

Thank you for all your help :-)

[Norie]

What does ClearScreen do?

[Norie]

Almost forgot this will toggle the caption, put it in the change event of the toggle button

Please Login or Register  to view this content.

[excelnat]

It selects the unlocked cells on Sheet 1 and clears contents and clears some dependent cells on other sheets. So if the previous user has saved the file with info in the cells, ClearScreen empties them, resetting the form (sheet 1):

Please Login or Register  to view this content.

[Norie]

Don't know if it'll make any difference but you could do that without selecting.

Please Login or Register  to view this content.

[excelnat]

Almost forgot this will toggle the caption, put it in the change event of the toggle button

Please Login or Register  to view this content.

That's fab!!! Thank you :-)

[excelnat]

Thanks for that, I'm just learning how to do things without Select as I know it speeds the calculations up, and I wasn't sure how to write the ClearContents bit without Select so I didn't want to ruin anything! So thanks for that, a great help :-D

Also, I think I've found a way around the toggle button problem...

In the BeforeClose event, I've changed the button to always be set to 'on' when the workbook is closed:

Please Login or Register  to view this content.

Seems to be working, even if it is a bit of a bodge!