Sudden 'Compile Error' message in browse sheets macro.

[henryBukowski]

Hi folks,

This afternoon one of my macro's went inexplicably wrong, having previously worked like a dream. I now get the error:

'Compile Error: Expected function or Variable.'

The line it highlights is:

Please Login or Register  to view this content.

The macro I'm using is Bob Philips' 'Browse Sheets' Macro to quickly choose which worksheet to go to.

It had been working fine, until I tried to use it as part of user-form.

When I double click a command button on said user form, this function was supposed to run: The code for the macro is as follows:

Please Login or Register  to view this content.

It appears as follows in the code for the command button I'm using:

Please Login or Register  to view this content.

What is annoying is that if I open a new excel book, paste the macro code into a new module in that book and run it there, it works perfectly - but it just won't work in the workbook I need it to work in!

I'd be truly grateful for any help or advice.

[Norie]

Have you called anything 'activesheet'?

[henryBukowski]

Have you called anything 'activesheet'?

Ah... yes, I have another(failed) macro called activesheet.

Is that not good?

[henryBukowski]

Norie, I deleted the culprit and I no longer get that error - I do however get a new one which is as follows:

'Form already displayed; can't display modally.

When I push debug, it highlights this which is y code for the worksheet activate event handler.

Private Sub Worksheet_Activate()
UserForm2.Show
End Sub

[Norie]

Using names of existing objects, methods, properties is not good and you should avoid it.

Even if you get away with it, ie no error messages, then you might find your code acting strangely.

As for the new error, stupid question but is the form open?

Are you displaying the form every time a particular worksheet is activated?

If you are when are you unloading/hiding it?

[henryBukowski]

Using names of existing objects, methods, properties is not good and you should avoid it.

Even if you get away with it, ie no error messages, then you might find your code acting strangely.

As for the new error, stupid question but is the form open?

Are you displaying the form every time a particular worksheet is activated?

If you are when are you unloading/hiding it?

Thanks Norie - so it's yes to your first two questions and re the third:

The userform is appearing when my front sheet, cunningly named "front sheet", is activated. So the following code runs when this occurs:

Please Login or Register  to view this content.

My user form then appears. One of the command buttons on the user form which has appeared is 'navigate clients'. When I click the button, the code runs as follows:

Please Login or Register  to view this content.

Does this make sense? There may be better way to do all of this but I don't know. I haven't got any code which re-hides the user form after it's been unhidden - perhaps I should do?

E.G, should the user form be hidden again when I click command button on the form?

Thanks

[Norie]

Is the only reason for the userform to run that code?

If it is you may as well unload it straightaway.

By the way, where did you get the code?

Do you know which version of Excel it's meant for?

The reason I ask that is that it's using an Excel Dialog sheet, which was kind of the predecessor to userforms.

Can you upload an example workbook?

That might clear things up.

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

[henryBukowski]

Hiya Norie - hope you're still with me!

You make a good point about the code - it's at least a decade old judging from the the read where I got it - and Bob Philips seemed to be name everybody knew at that time. So as for the version, it must have been even earlier than excel 2003. However, and I should emphasise this, it has been working like I imagine it was supposed to - it gives me a browser tool where I can chose which tab to navigate to. This is very helpful to me because when the workbook is finished it could have hundreds of sheets (client sheets)

In terms of the use: The structure of my workbook is that I have lots of client sheets where data is entered - and on the front sheet I have the user form which facilitates a few basic functions - one of which is the 'navigate clients' so that the user can quickly switch sheets from a great range. There's currently one additional function which is an 'add clients' sheet which adds a blank form (not blank sheet) to the workbook.

As for the attachment, what I've done is just posted a virtually blank document but which activates the user form when you go to the front page. You won't see any client forms in the document.

In downloading this workbook will the macro's and user forms just work straight off for you?
because this would be another question I have, though I don't know whether I should start a new thread - but I want to send my completed workbook to a number of people working in different locations and for all the macro's to work without me having to go there and add them via vba. I've digitally signed my macro's which works to an extent but doesn't seem to work when I send the document to people.

Thanks mate.