Assigning macros to a dynamically created userform

[moosemaster23]

Hi there.

I'm trying to make a userform which will come up with a list of all sheets in the workbook (both hidden and unhidden) such that when you click on the name of a sheet the sheet is then selected.

Essentially I'm trying to create a contents page.

The code I have so far will populate the Userform with the names of the sheets:

Please Login or Register  to view this content.

This code works and the spacing is good. Now I want to add a macro to each label which goes along the lines of :

Please Login or Register  to view this content.

Suggestions on adding this code dynamically?

[JosephP]

it would be much easier to add a listbox and button to the form at design time and add code to select whichever sheet is selected in the listbox list (which can also autopopulate when the form is loaded)

[moosemaster23]

it would be much easier to add a listbox and button to the form at design time and add code to select whichever sheet is selected in the listbox list (which can also autopopulate when the form is loaded)

Hi, thanks for your reply.

Whilst this would probably be easier, I'm still keen on working under my original idea (I think it's neater to have a single click than two clicks).

Do you have any ideas on how to create a macro for each "click" event?

Thanks,

Tom

[Norie]

Tom

Why would you need a double click with a listbox?

Just use the click(or change) event of the listbox.

[moosemaster23]

Tom

Why would you need a double click with a listbox?

Well you'd have to select the sheet you wanted (click the list box) then you'd have to click the command button. It would be easier to just scroll through the list of sheets (there could be 40 lets say) and then just click the name of the sheet and it takes you to the sheet.

[Kyle123]

Whilst I agree with Joseph and Norie, here's the alternative you were asking about:

Userform Initialize:

Please Login or Register  to view this content.

Class cNavigation:

Please Login or Register  to view this content.

I still think you'd be better off with a listbox though

[Norie]

What command button?

[moosemaster23]

Whilst I agree with Joseph and Norie, here's the alternative you were asking about:

I still think you'd be better off with a listbox though

P.S this raises a question I hadn't thought about before, why is xlSheetVisible -1, shouldn't it be 1 for true - actually just realised that true is -1 in VBA

Thanks Kyle!

When adding this code to my UserForm, I haven't come across Class cNavigation before (I'm a bit of a novice still). Where should I put the code that your have written? See picture attached:

UserForm code.jpg

Thanks again!

[Kyle123]

You need to create a class module and name it cNavigation, the code then goes in there

[moosemaster23]

You need to create a class module and name it cNavigation, the code then goes in there

omg exciting!

This is most excellent.

I am in your debt good Sir.

[Norie]

Here's the code you would need for a listbox.

Please Login or Register  to view this content.

[moosemaster23]

You need to create a class module and name it cNavigation, the code then goes in there

Hello again,

I might have made a mistake but the code doesn't appear to update the sheets to more than just the standard "Sheet 1", "Sheet 2" and "Sheet 3". Therefore when there are different named sheets, it bugs out because it is trying to access a sheet named "Sheet 1" when such a sheet doesn't exist...

Does the code include memory at any stage so that it remembers the sheet setup of the first time it was activated and then keeps that? (Note that I'm trying to make this into an AddIn, will this not work in this set up?)

See pic attached.

Can you check this?

issue.jpg

Thanks,

Tom

[JosephP]

Kyle's code lists the sheets in the workbook where the form is. perhaps you want to use activeworkbook instead of thisworkbook

[Kyle123]

IT works fine on mine, I suspect you have switched workbooks? Are you expecting the code to work on any workbook without updating it?

[Kyle123]

Variation on Norie's code to work for the active workbook:

Please Login or Register  to view this content.

[moosemaster23]

IT works fine on mine, I suspect you have switched workbooks? Are you expecting the code to work on any workbook without updating it?

Yes. I wanted to make an AddIn.

[Kyle123]

Does my variation on Norie's code do what you want? it's far more simple than adapting my original code

[moosemaster23]

Does my variation on Norie's code do what you want? it's far more simple than adapting my original code

Thanks Kyle, it does work.

The code you provided does what I need. I don't really understand why there's such an advance in difficulty when looking at the problem as originally stated but that doesn't matter.

Thanks a lot everyone!

[Kyle123]

It's more difficult since you need to remove the controls (labels) that you've added each time the workbook is changed. This adds complexity, whereas a listbox has a clear method that handles it.