VBA Works for Me But Not Others

[jessebh2003]

I have two macros each with an error.

The first macro has a very long formula that works correctly for me but when others run the macro, they get an error message. Here's the formula:

=COUNT(FILTER(ROW(Data!$AQ$3:$AQ$1000),(Data!$AQ$3:$AQ$1000="Yes")+(Data!$AQ$3:$AQ$1000="No")+(Data!$BA$3:$BA$1000="Yes")+(Data!$BA$3:$BA$1000="No")+(Data!$BK$3:$BK$1000="Yes")+(Data!$BK$3:$BK$1000="No")+(Data!$BU$3:$BU$1000="Yes")+(Data!$BU$3:$BU$1000="No")+(Data!$CE$3:$CE$1000="Yes")+(Data!$CE$3:$CE$1000="No")+(Data!$CO$3:$CO$1000="Yes")+(Data!$CO$3:$CO$1000="No")+(Data!$CY$3:$CY$1000="Yes")+(Data!$CY$3:$CY$1000="No")+(Data!$DI$3:$DI$1000="Yes")+(Data!$DI$3:$DI$1000="No")+(Data!$DS$3:$DS$1000="Yes")+(Data!$DS$3:$DS$1000="No")+(Data!$EC$3:$EC$1000="Yes")+(Data!$EC$3:$EC$1000="No")+(Data!$EN$3:$EN$1000="Yes")+(Data!$EN$3:$EN$1000="No")))

In the VBA, the code is structured:

Range("J9").Select
ActiveCell.FormulaR1C1 = "=COUNT(FILTER(ROW(Data!R3C43:R1000C43),(Data!R3C43:R1000C43=""Yes"")+(Data!R3C43:R1000C43=""No"")" & _
"+(Data!R3C53:R1000C53=""Yes"")+(Data!R3C53:R1000C53=""No"")+(Data!R3C63:R1000C63=""Yes"")+(Data!R3C63:R1000C63=""No"")" & _
"+(Data!R3C73:R1000C73=""Yes"")+(Data!R3C73:R1000C73=""No"")+(Data!R3C83:R1000C83=""Yes"")+(Data!R3C83:R1000C83=""No"")" & _
"+(Data!R3C93:R1000C93=""Yes"")+(Data!R3C93:R1000C93=""No"")+(Data!R3C103:R1000C103=""Yes"")+(Data!R3C103:R1000C103=""No"")" & _
"+(Data!R3C113:R1000C113=""Yes"")+(Data!R3C113:R1000C113=""No"")+(Data!R3C123:R1000C123=""Yes"")+(Data!R3C123:R1000C123=""No"")" & _
"+(Data!R3C133:R1000C133=""Yes"")+(Data!R3C133:R1000C133=""No"")+(Data!R3C144:R1000C144=""Yes"")+(Data!R3C144:R1000C144=""No"")))"

We tried connecting all of the rows of code into one long row but got the same error.

The second macro replaces merge fields in Word with data from Excel, which all works correctly until the macro gets to the code to save the Word document.

.SaveAs2 Environ("userprofile") & "\Desktop\ROL Evaluation Report" & "_" & Sheets("Data").Range("B1").Value _
& "_" & Format(Now, "yyyy-mm-dd hh-mm") & ".docx"

The code works find for me but they get the error:
"Run-time error '5152':

This is not a valid file name.
Try one or more of the following:
*Check the path to make sure it was typed correctly.
*Select a file from the list of files and folders."

Can someone help me solve these two errors? Thanks!!!

 

[Joe4]

It sounds like the other users may not have the same paths as you.
Did you check that drive path on the other user's computers, and verify that it is valid, and thet can write to it?

Also, are they using the same version of Excel as you?

And what do they have in cell B1?

 

[jessebh2003]

It sounds like the other users may not have the same paths as you.
Did you check that drive path on the other user's computers, and verify that it is valid, and thet can write to it?

Also, are they using the same version of Excel as you?

And what do they have in cell B1?

Hi Joe4,

We checked the path on their computer by going to Visual Basic and opening the Immediate Window. They typed: ?environ("userprofile") - the only thing different was their user id (e.g., mine is ABC123 and theirs is XYZ987). I used Environ("userprofile") in the code so that regardless of who was running the macro it would save to their Desktop.

Yes, we are both using the same version of Excel - Office 365 for Enterprise.

B1 is the name of the conference the report is for. The text in that cell varies depending on the conference name. I thought pulling the conference name into the file name would be better than hard coding something more generic.

If there's a better way, I'm open to ideas. Appreciate any help! Thanks.

 

[Joe4]

On the computer where it works, go into the VB Editor, and click on Tools -> References and note all the libraries that are selected.
Now do the same on a computer that does not work. Does it show all the same libraries selected? If not, find them in the list and select them.

If that is not the issue, try temporarily updating your code, replacing this:

.SaveAs2 Environ("userprofile") & "\Desktop\ROL Evaluation Report" & "_" & Sheets("Data").Range("B1").Value _
& "_" & Format(Now, "yyyy-mm-dd hh-mm") & ".docx"

with this:

Dim fname as String
fname = Environ("userprofile") & "\Desktop\ROL Evaluation Report" & "_" & Sheets("Data").Range("B1").Value _
    & "_" & Format(Now, "yyyy-mm-dd hh-mm") & ".docx"
MsgBox fname
.SaveAs2 fname

This stores the calculated file name in a variable first, and spits it out to a MsgBox before trying to save it.
Let us know exactly what that message box shows.

 

[Marc L]

​

Or just a bad name in cell B1 …​

 

[Joe4]

Or just a bad name in cell B1 …

...which is why I want to see what that Message Box is returning...

 

[Marc L]

​

Yes as it is the more obvious error source …​

 

[jessebh2003]

Thanks! The file name message box displayed:

C:\Users\XYZ987\Desktop\ROL Evaluation Report_Multiple Sclerosis Center: 2021 Virtual Symposium_2021-05-14 13-00.docx

So yes, bad cell name given the colon, which would need removed from the file name. Is there a way to replace the colon (or any other prohibited character) with an underscore?

 

[Joe4]

I suspected that was the culprit.

You could use code like the following to restrict it: Textbox restrict to alphanumeric
or check out this custom function by Rick: Allow only characters, numbers and spaces in a cell

 

[jessebh2003]

Thanks, Joe4. Functions in VBA is outside my knowledge base so I'm going to have to figure some else out.

Any thoughts on the other macro in my original post?