Active X button and Public statement

[Romano_odK]

Good evening,

For conversion to XML I got a macro. I would like to put this macro in a Active X button, but it refuses to run. (it does run with a normal button) The first error I got is on the Public statement. Now this is a small part of the macro, but can this solved?

Thank you for your time and efforts.

ROmano


Public Itemcode As String
Public SalesPrice As String
Public CostPrice As String
Public SupplierMain As String
Public SupplierCode As String
Public SupplierPrice As String

Public Itemrow As Integer ' Counter for actual row
Public SaveDir As String ' Header Cell (2,2)
Public ItemCurrency As String ' Header Cell (4,2)
Public ItemResource As String ' Header Cell (6,2)
Public Action As Boolean ' Flag

 

[GWteB]

There is no clear answer to this because the rest of your code is missing.
The Public statements ensure that variables are declared at project level with a global scope. Therefore the Public statement cannot be used at procedure level (an ActiveX button click event handler is a procedure). Because the variables are declared that way it's quite possible the rest of your code consists of multiple procedures (macros), but we don't know for sure, so changing scope could lead to other issues.

I would recommend that you familiarize yourself with the declaration of variables, their scope and lifetime.

VBA Variable Scope and Lifetime - wellsr.com

VBA Variable Scope tries to answer the question Where do I want to use my variable? There are 3 levels of variable scope - Procedure, Module and Project.

wellsr.com

 

[Romano_odK]

There is no clear answer to this because the rest of your code is missing.
The Public statements ensure that variables are declared at project level with a global scope. Therefore the Public statement cannot be used at procedure level (an ActiveX button click event handler is a procedure). Because the variables are declared that way it's quite possible the rest of your code consists of multiple procedures (macros), but we don't know for sure, so changing scope could lead to other issues.

I would recommend that you familiarize yourself with the declaration of variables, their scope and lifetime.

VBA Variable Scope and Lifetime - wellsr.com

VBA Variable Scope tries to answer the question Where do I want to use my variable? There are 3 levels of variable scope - Procedure, Module and Project.

wellsr.com

Good evening,

Thank you for your reply. Not an easy topic but I will try understand it all.

Romano

 

[GWteB]

You are welcome and good luck!