View Full Version : Set statement
av8tordude
06-04-2020, 07:45 AM
I have this code place in the Private Sub UserForm_Initialize() procedure. I reference this code in a few other procedure in the same userform, so I decided to reduce the redundancy and place it in the initialization. Sometimes it works but i notice it would create error alert. If I place this code in the procedure of the error, it runs without any issues. Can someone explain why?
Thanks
Run-time error 91
Object Variable or With block variable not set
Set Wks = ActiveSheet
Whenever possible, avoid referring to any "Active" object
Set Wks = Sheets("Sheet1")
It is possible to have an open UserForm, with no active sheets
You don't need an Object variable for an object you can refer to directly.
Sheet1. in your code suffices.
royUK
06-05-2020, 05:32 AM
I would not declare a variable for ActiveSheet.
If you want to declare a variable to be used by other procedures in the UserForm then place it at the top of the code module, not in any Procedure.
Option Explicit
Dim Wks As Worksheet
Private Sub UserForm_Initialize()
Set Wks = Sheet1
End Sub
Paul_Hossler
06-05-2020, 11:10 AM
I have this code place in the Private Sub UserForm_Initialize() procedure. I reference this code in a few other procedure in the same userform, so I decided to reduce the redundancy and place it in the initialization. Sometimes it works but i notice it would create error alert. If I place this code in the procedure of the error, it runs without any issues. Can someone explain why?
Thanks
Can you open your userform when there's different active sheets?
e.g.
If "Sheet1" is active then you want the UF to act on "Sheet1"
If "Sheet2" is active then you want the UF to act on "Sheet2"
etc.?
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.