Using a userform as a variable in a module

R

Richhall

Hi, I am new to VBA and am trying to tidy up some code as I think I
should be using modules, rather than putting everything within a
worksheet or userform. I have the following code:


Private Sub EnquiriesPCP_Click()
Workbooks("Projects.xls").Activate
Sheets("Archive).Visible = False
PCPForm.Hide
Unload PCPForm
Sheets("Enquiries").Select
Load PCPEnqForm
PCPEnqForm.Show
End Sub

EnquiriesPCP is a button on the userform PCPForm. Clicking this opens
another Userform called PCPEnqForm. I have a number of buttons
opening other forms from PCPForm, and these other forms also have
buttons opening the other forms on PCPForm, as well as PCPForm.
Rather than writing similar code behind every button on all 10 forms,
I thought it would be better to have the main code in a module, and
just edit the variable on each individual form.

i.e

Module 1

Sub gotoSheet()

Workbooks("Projects.xls").Activate
Sheets("Archive").Visible = False
CurForm.Hide
Unload CurForm
Sheets(NewSheet).Select
Load NewForm
NewForm.Show

End Sub

Then behind each button is what I am now stuck with as I am not sure
how to create the correct variable and then pass as a variable to the
module, as everything I have tried seems to come back with errors.

I've guessed using the below, but tried Dim CurForm as Object,
UserForm and PCPForm and not really sure what I am meant to do.
Apologies but I have no VB experience and this is all just from using
help.

Public Sub EnquiriesPCP_Click()

Define Variables

gotoSheet

End Sub

Can anyone help please? I am also under the impression that the syntax
in the module would be incorrect if I can even define the sheet name -
Sheets(NewSheet).Select.

Thanks

Rich
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top