Popup Window

[jsc0624]

Hi to all,

Is it possible for me to write a code in vba that will popup a form when I click sheet 1 as an example that will show an option window to navigate sheet 2 or sheet 3? How can I do that?

Thanks for the help.

[Ken Puls]

I've attached an example of what I think you mean. For reference, the code should be set up using a class module to avoid additional sheets getting unweildly, but this should start you off. The keys to making it work:

-Userform is shown as non-modal. This allows you to work in the grid while the form is shown
-The ThisWorkbook module holds a Workbook_SheetActivate that fires the userform whenever any worksheet is activated

Cheers,

[jsc0624]

Thanks very much! It's exactly what I need. It would be a great start for me.

[Ken Puls]

You're welcome.

Just for reference, if you have more than a few sheets, you might want to try using a listbox instead. (It will scale better for larger quantities.)

Also, in the code I provided, it will activate the first, second or third sheets. So if you moved Sheet 3 before Sheet 1, the order in the userform may not match up to the names.

If you are concerned about this, you may want to change the code names on the sheets, then refer to those in the code.

Cheers,

[tstav]

I have a KB entry that may serve several of your requirements or just give you several ideas. It uses a floating listbox instead of Option buttons.
http://vbaexpress.com/kb/getarticle.php?kb_id=998

Regards,
tstav

[Bob Phillips]

How about


Application.Commandbars("Workbook Tabs").ShowPopup

[Ken Puls]

That's certainly a much less labour intensive route, Bob. :yes

[Bob Phillips]

You know me, I like the snappy one-liners.