Use sheetname to open a Userform

[aph]

Hi

I want to Show a different Userform based on the sheet its opened from. I have tried code below but the UserForm name is in the wrong format but can’t find the correct Dim, can anyone help please? The target Userform is ‘cables_form’.

Snippet:-

Tried both of these:-

Please Login or Register  to view this content.

I am using Excel 2000

Andrew

[royUK]

Follow the Forum Rules & wrap code with Code Tags

can I ask why have you different forms for different sheets?

[aph]

Hi Roy

The workbook has 4 sheets, each for a different type of survey and different data to enter. Also each column has different rules applied to it i.e. max 4 characters, change OS map references from alpha – numeric to all numeric etc.

I could have a different macro button for each page but thought this would be easy and just gather the sheetname and goto the correct Userform.

Andrew

[royUK]

here's one way

Please Login or Register  to view this content.

[aph]

here's one way

Please Login or Register  to view this content.

Hi

I tried your suggestion but it didn't seem to work so I tired this:-

Please Login or Register  to view this content.

but no joy but is probably wrong use of 'Case' anyway. So I ended up with :-

Please Login or Register  to view this content.

Thanks for your help, Andrew

[royUK]

The Select case is correct, you haven't given sWsName a value as far as I can see. The code I posted works fine for me.