CODEWITHSUNDEEP
vbaexcel
Cutter
Cutter

Reputation: 1820

What's the best way to store settings on a per-worksheet basis?

I need to temporarily store my macro's settings for each worksheet. I thought about extending the active sheet's properties to store the settings there. The goal would be to read a property such as ActiveSheet.Setting1. I've tried wrapping the ActiveSheet object in a class:

"cCustomSheet" class module:

Public WithEvents WS As Worksheet
Public Setting1 As String

"Module1" module:

Dim ActiveWS As cCustomSheet
Sub test1()
    Set ActiveWS = New cCustomSheet
    Set ActiveWS.WS = ActiveSheet
End Sub
Sub test2()
    MsgBox ActiveWS.WS.Name
End Sub

This code works without an error, but ActiveWS should be updated to the new active sheet if the user activates another sheet, and it's not. If I remove the Set keyword from the line Set ActiveWS.WS = ActiveSheet, I get the following error:

Error n° 91: Object variable or With block variable not set

How should I do it, and is this even possible?

I also thought about using ActiveSheet.CustomProperties but this seems rather clumsy since you can't retrieve a setting by its name (without looping through all of the items), and you have to rely on index numbers.

Thanks in advance.

Update: I should add that my macro will be stored as an Excel Add-in, therefore I won't have access to the code of the workbooks of the users. I cannot take advantage of the Worksheet_Activate() event to keep track of the active sheet. Also, the settings would preferably be lost or reset to their default values when the workbook is reopened, that's why I can't save them inside the user's file (in a hidden sheet for example).

Upvotes: 2

Views: 1598

Answers (4)

Cutter
Cutter

Reputation: 1820

Thanks everyone for your suggestions. They have helped me finding a way to store my settings for each worksheet. I've used ActiveWorkbook.CustomDocumentProperties to assign a random ID to the active workbook. Each worksheet is identified by the concatenation of the workbook's ID and the sheet's codename (which is unique inside a workbook). The settings for the active worksheet are added to a collection where the sheet's ID serves as the index, so that they can be retrieved easily.

This is not 100% safe since two workbooks can hypothetically get the same ID, since it's a random number. The workbook's ID is also saved to the Excel file. It becomes useless after the workbook is closed but "pollutes" the custom properties list of the file.

I also needed to store the address of the column holding the values on which to apply my macro. This is set by the user. Instead of storing the absolute address of the range, I'm using the .ID property of the first cell of the column. This way, if the user moves the cells, the ID sticks to the values. The downside is that I have to cycle through every cell of the first row of the selection to check if a cell holds an .ID value, which is quite inelegant. The .ID property is gone after the workbook is reopened, which is fine for me because I didn't want the settings to be persistent.

Upvotes: 0

assylias
assylias

Reputation: 328669

As your macro is in an Excel Addin, you could store the information in the sheets of that addin, which are not visible. You can even make that sheet "very hidden" if you want to make sure nobody unhides it by mistake. For example, you could name the Sheet1 of your addin "Parameters" and store on each line, the workbook name in column A and the parameter in column B.

By the way, this makes sure that the data is not lost if an error in the code causes all the variables to be reset.

Alternatively, if you need that setting to be available after closing and reopening Excel, which does not seem to be the case, you could create a very hidden sheet in the workbook and store your data there, but that requires changing that workbook.

Upvotes: 0

Jesse
Jesse

Reputation: 1935

I had a project where I needed to send out sheets with settings but the macros were in my master sheet, not the distributed sheets.

I found storing the settings in a named range worked very well. You can store any string, number or formula and you can even use the values in the sheet if required.

Upvotes: 0

Doug Glancy
Doug Glancy

Reputation: 27478

To get you at least part way there:

In Module1 just have the line:

Public ActiveWS As cCustomSheet

Notice that it's now declared as Public.

In the ThisWorkbook module try this code:

Private Sub Workbook_Open()
Set ActiveWS = New cCustomSheet
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Set ActiveWS.WS = ActiveSheet
With ActiveWS
     Debug.Print .WS.Name
    .Setting1 = "something important about this sheet"
End With
End Sub

The thing to note is you'll lose whatever is in Setting1 for one sheet once you activate the next, but if you just want to access the properties while the sheet is active, this should do it. If not, you can add all the sheets to a Collection or Dictionary, which will keep the properties for all of them accessible as long as the Collection or Dictionary exists. So... let us know a little more about your goals here and I'll try to help you think through the best option.

EDIT: In a more general answer to your question, I tend to store settings in worksheet names, which can be completely hidden from users if desired. This could be a good way to go especially if you need the settings to persist between times that the workbook is opened.

Upvotes: 4

Related Questions