Results 1 to 4 of 4

Thread: Declarations

  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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()

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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.

  3. #3
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I'll stich with the safer option, less hassle. Thanks

  4. #4
    Lounger
    Join Date
    Sep 2008
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You could make ws1 and ws2 functions rather than variables.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •