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
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