Hide OptionButton

[kjg]

Hi Guys,

I have an OptionButton for "New Sheet" on my worksheet, when selected and after clicking the "Go" command button, I get a copy "to end", of the active sheet without the data.

What i would like is for the OptionButton to be only visible on the last (rightmost) sheet, then when this sheet is "copied to end" the OptionButton is again ONLY on the last (rightmost) sheet but not on / visible on previous sheets.

TIA
Keith

[Winon]

Hello kjg,

That would depend on what controls you are using. Would it be Form controls, or Active X controls.

It would also be good if you could attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

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

View Pic

Regards.

[kjg]

Hi Winon,

Thanks for your reply.

I understand that it would be much better with a sample workbook. I did try to sanatise my workbook, but with references in the macros to data in the sheets also, I found it was going to be a large task, so "hope" you can help without it.

The controls that I am using are ActiveX controls. As said before, the OptionButton should only appear on the last (rightmost) sheet and not on the preceeding sheet.

Cheers,
Keith

[LJMetzger]

Hi Keith,

For completeness, two routines follow:
a. Active X Option Buttons (as your requested)
b. Forms Control Option Buttons

In both cases you have to know the 'Option Button' NAME, and the NAME must be the same on all sheets.

Lewis

For Active X Option Buttons:

Please Login or Register  to view this content.

For Forms Option Buttons:

Please Login or Register  to view this content.

[kjg]

Thanks for that LJMetzger,

You guys never cease to amaze me, it worked great first time and each time, perfectly.

Is there any way to have it do its thing “if” the last sheet were deleted, so the OptionButton would come back to the previous last sheet, sort of like the undo, only this may be after a save.

I do appreciate your help, thanks,
Keith

[Winon]

Hello kjg,

LJMetzger has provided you with a very good piece of good, and yes, even if you were to delete any of the farmost Sheets, the OptionButton will only show on the remaining last sheet.

By using the Active X Option Buttons code in a standard module, one option to accomplish this would be to add the Code below in ThisWorkbook Code:

Please Login or Register  to view this content.

Nice job!, LJMetzger.

Regards.

[LJMetzger]

kjg and Winon,

Thank you both for the kind words.

I don't think 'Workbook_SheetActivate()' is called when the Workbook opens. If this is the case, then the routine needs to be in 'Workbook_Open()' also.

Please Login or Register  to view this content.

Lewis

[kjg]

Thanks again guys.

I used the code as suggested by Winon and it "seems" to be working perfectly. If I add a sheet and delete, if I close the workbook then re-open it then add a sheet and delete it, it still performs as expected.

Brilliant, thank you both.

[Winon]

Hello kjg,

Thank you for the feedback, and for adding to my Reputation.

You are welcome, glad we could help.

Thank you also for marking this Thread as "Solved".

Regards.