Declarations

Topic

New Reply

Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = Worksheets(“x”)
Set ws2 = Worksheets(“y”)

I want to use the above in all of the below, Can I set these once instead of individually?

Public Sub UserForm_Activate()
Public Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
Public Sub SearchButton1_Click()

Reply | Quote

Replies

You could declare the variables as public at the top of a standard module, and set their value in the Workbook_Open event procedure in the ThisWorkbook module.
But you’d have to be very careful: if an unhandled error occurred, the variables would lose their values.

Reply | Quote

I’ll stich with the safer option, less hassle. Thanks

Reply | Quote

You could make ws1 and ws2 functions rather than variables.

Reply | Quote