Thread: Passing variables and values between sheet code and module code

hello:

I really need your help.

I have some buttons in different sheets in an excel file, each button has its own code, that is the reason I can not move the code related to each object to another location (sheet or module).

And I have one piece of code in Module1 (Auto_load) in order to execute automatically this routine every time file is opened. Inside "auto_load" routine I initialize some values of some check buttons,options buttons and positions of some objects in diferent sheets, but I can not pass the value of variables between Module and Sheet's code even when I declare as public variables and/or function.

I have the following structure:

VBA Project
------Microsoft Excel Object
----------Sheet1 <-I have code here
----------Sheet2
----------Sheet3 <-I have code here
------Modules
----------Module1 <-I have code here
----------Module2
----------Module3

How I can pass the value of some variables between Module and sheets??

Thanks in advance.

Declare the CONSTANT values outside of the individual macros.

Public Const LastRow As Long = 1

This creates the reference and gives it a beginning value. You can update the constant's value in any other macro at any time, but its current value is available at all times. That may be oversimplified, but enough to get you started, maybe?

Click on GO ADVANCED and use the paperclip icon to upload your workbook. Point out the items in question in the workbook, I'll take a look.

Public variables need to be declared in a regular old code module, not in an object module (Sheetx, ThisWorkbook, ...). Then they are visible throughout the project.

There is something off about the entire Sheet "Forms" ...it doesn't behave in a way I can wrangle. See, the same idea that you're attempting I put on the attached sheet. This very simple "auto_open" module macro takes care of setting the buttons and checkboxes for me when the sheet is opened.

I simply linked the objects to a range of cells, then set the value of the entire range to TRUE in the macro:

Option Explicit

Private Sub auto_open()

    Sheets("Form").Range("AA1:AA4").Value = True

End Sub

Try it, open this book, turn everything the wrong way, save it, then reopen it.

this same techniques "sort of works" on your sheet, but when the sheet opens, it not only sets the items on that you want, it also leaves the alternate toggle buttons selected. Very odd...

to force the control setting on open you can just use

Thisworkbook code module

Private Sub Workbook_Open()

    With Sheet1
        .obes_sa1.Value = True
        .obsa1.Value = True
        .cbme1.Value = True
        .cbme2.Value = True
    End With
End Sub

If that takes care of your need, be sure to EDIT your original post (Go Advanced) and mark the PREFIX box [SOLVED].






(Also, use the "scales" icon in our posts to leave Feedback, it is appreciated)