Conflicts in coding between different workbooks

[Nick Vittum]

Background: (Maybe I'm supposed to say this somewhere else, but I can't find an introductions thread.) I'm new here. I've used this site for years, found many answers here, but this is my first time posting. The work I do with Excel (these days) is for my own use and I'm not brand new but still a relative novice to coding.

That said, my question at this point is a general one. I have a workbook for my own accounting purposes that I've put a lot of work into automating. But it's been an evolutionary process; no clear design in advance. What I'm running into now is that the coding which is basic to the way the workbook functions seems to conflict with other workbooks, if I have another open at the same time. For instance, a command button on a userform in the accounting WB will "bleed over" and cause things to happen in the other workbook. It's too complex and at this point too confusing to ask specific questions. (I might ask them when I understand better what's happening.) What I'm asking for now is if there are any guidelines, basic rules of thumb, whatever, I can find somewhere that will help me redesign/reword my coding so that it won't affect other workbooks?

Thanks!

 

[Anthony47]

You have to explicitally address the Workbook and the Worksheet, when it is not granted that they are the active ones while the macro runs; if you just say Range("A7").Clear you will clear the cell of the active worksheet (of the acrive Workbook)
You can use "ThisWorkbook" to point to the workbook that hosts the macro; eg: ThisWorkbook.Sheets("List").Range("A7").Clear
If you use the "CodeName" for a sheet, then this will automatically be anchored also to the workbook that hosts the macro; eg: Sheet1.Range("A7").Clear (beware: don't assume that CodeName and Sheet Name corresponds, look into the Project-VBAProject window)

So you have to review the code to take this into consideration.

Bye

 

[JLGWhiz]

Some things to understand about vba code if you have more than one workbook open while code is running.
1, If the code does not qualify ranges and worksheets with their parent objects, then vba will automatically default to the active workbook and/or worksheet.
2. The use of variables at the beginning of the code to 'lock in" the qualifying parent refences will prevent the inadvertant undesired default by vba.
3. The object variables should then be used throughout the code and avoid such references as ActiveSheet, ActiveWorkbook, selection and other such references which automatically default to the active worfksheet.
See more here

10 ways to reference Excel workbooks and sheets using VBA

Excel offers myriad options for referring to workbooks and sheets in your VBA code. See which methods make sense in which situations.

www.techrepublic.com

 

[Nick Vittum]

Thank you both. I have been trying to use "ThisWorkbook" wherever possible, but the results seem spotty.
@ JLGWhiz, I had glanced through that TechRepublic page earlier. I will give it a more detailed read. I think the bit about assigning variables to the workbook name in question is what I'm reaching for. I just (so far) haven't been able to figure out how to do it.

Let me give you both one example I'm wrestling with right now, where I did use "ThisWorkbook": The Workbook_Open code calls two macros, one which hides the ribbon, and another that minimizes the workbook(2020.xlsm) and shows the Userform that is the primary interface with the workbook. All well and good. but if I open another workbook while "2020.xlsm" is open, two things happen: the new workbook also opens with ribbon hidden, and the minimized "2020.xlsm" is restored to a normal window. The macro to hide the ribbon as as follows—

Sub Ribbon()
    With ThisWorkbook
        Application.ExecuteExcel4Macro "Show.ToolBar(""Ribbon"", False)"
    End With
End Sub

—and the code to show it again if need be (behind a button on a very small Userform) is this:

Private Sub CommandButton1_Click()           ' shows the ribbom and hides this form
    With ThisWorkbook
        Application.ExecuteExcel4Macro "Show.ToolBar(""Ribbon"", True)"
        Hide_UF_QAT
    End With
End Sub

So in the first place, I don't like the Ribbon macro from affecting other workbooks, and I don't know how to prevent it. More strangely, if I click CommandButton1, it restores the ribbon on the other workbook. Only on a second click does it restore the ribbon on the workbook to which it belongs. Not a bad thing to easily restore the ribbon on the workbook where I didn't want it hidden in the first place. But the fact that it behaves this way is one of several indicators that my code in 2020.xlsb is acting on other workbooks in unexpected ways.

Thanks for your feedback

 

[Anthony47]

You are using Office 365, so for sure you are working with the "Single Document Interface", see Programming for the Single Document Interface in Excel
This means that each window (workbook) has its own interface, and you could forget about modifying the interface when you activate a specific workbook; just customize the Ribbon and QAT and save that customization not "For all the documents" but for the current document only (there is a drop down list at the top rigth of the customization window).

In your Sub CommandButton1_Click routine the use of With ThisWorkbook /End with is useless because the commands under the "With" don't refer to a workbook.

Bye

 

[Nick Vittum]

Thank you Anthony.

Your last comment first: Re: the "With ThisWorkbook": what I was hoping to accomplish was "do this only in this workbook, nowhere else."But do I understand you correctly that it doesn't work that way? (If so, I guess I don't understand "With" statements at all.)

Re: customizing the ribbon in Options. I couldn't find any option there to hide the ribbon altogether and save it for that specific document. I did find a command to "Hide Ribbon", added to my Ribbon, used it, and saved the document. But the next time opened, the ribbon was restored, so the purpose defeated. I suspect you were telling me something else; but I couldn't find it. Could you explain?

 

[rlv01]

How much of your code is ancient pre-vba Excel 4 macros? In my experience those are nothing but trouble and should be re-written in VBA as soon as possible.

 

[Nick Vittum]

How much of your code is ancient pre-vba Excel 4 macros? In my experience those are nothing but trouble and should be re-written in VBA as soon as possible.

None really. There's one macro for cell formatting that I used the macro recorder for because I couldn't figure out how to write it. But it's behind a command button, isn't used much, and so far has caused no problems. Thanks

 

[Nick Vittum]

@

*

I should add: I can live without the ribbon being hidden on open. It was just a little tweak I liked. What I'm much more concerned with, in a general way, is learning to write my code so that one workbook doesn't have unintended effects on another.

 

[Anthony47]

I am sorry, but I gave you a wrong information: indeed, even with the Single Document Interface, the Ribbon is common to all the documents, so if you modify the ribbon while in workbook1, when you move to workbook2 it will have the modified ribbon.

To keep the modification within a specific workbook you have to play with its "Events" available within the vba module ThisWorkbook. Namely you should use these events:
-Workbook_Activate and Workbook_Open, to modify the ribbon
-Workbook_Deactivate and Workbook_BeforeClose, to restore it

What you can customize at "Workbook level" is the QuickAccessBar; so you can put in this bar commands that will be shown only in the interface of this workbook
Search for "excel qat customization" for instructions
To keep a customization at workbook level you have to set the "scope" of the modification; look at the video in this page and stop it at second 32:

Customize the Quick Access Toolbar - Microsoft Support

Add, remove, organize and reorder commands on the Quick Access Toolbar.

support.office.com

At the top right you see a dropdown that shows "For all documents (default)"; but there you can also choose "this current document only". The default option will set the modification in every interface; the second one will keep it within the current workbook and will be saved in it, and will not affect other workbooks interface.

If you wish to modify the ribbon, then I don't master this area so please don't accept my suggestions ; search instead for "excel ribbon customization" or wait for wiser suggestions here in the forum

My initial suggestion (message #2) was referring on how make sure that a macro refers to the "right" workbook & worksheet.
I don’t know if this is your concern, if it is then the basic concept is "always address the workbook+the worksheet while referring to a range of data
You do that using

Workbooks(NameOfWB).Sheets(NameOfWS).Range("A2")

Or using intermediate association

Set myWB = Workbooks(NameOfWB)
myWB.Sheets(NameOfWS).Range("A2")


'or

Set myWSh = Workbooks(NameOfWB).Sheets(NameOfWS)
myWSh.Range("A2")

or using ThisWorkbook to refer implicitally to the workbook where the macro is hosted

ThisWorkbook.Sheets(NameOfWS).Range("A2")

Or using "With" With statement (VBA)

With ThisWorkbook.Sheets(NameOfWS)
    .Range("A2").Value = 33
    .Range("B2").ClearContent
    .Range("A2").Interior.Color = RGB(255, 255, 0)
End With

And maybe many more methods. How doing that is mainly a matter of programming styles, even though some methods result in more readable code

Hope I didn't confuse you more that what I was wishing to do…