VBA error not making sense

[mhessnm]

Hello, I have a form on a spreadsheet in Excel that someone fills out in cells. I want to make the date and time cells highlight yellow when someone forgets to fill them in and exit the sub with a message to fill in the fields.

I swear this code worked last week when I tested it before uploading so others could use. But today, I'm getting an "application-defined or object defined error."

Here is the beginning of the code. In this I set a variable for the name of the sheet (logName) and another variable for the hidden sheet where the form data is copied (masterName). There are three sheets with date and time fields in the same cells on each sheet, and I make sure the cell is the correct background color before proceeding. However, the red text below is where the code hangs up. I don't see anything wrong with my code, and as I wrote above, it worked last week.

Why is it giving me this error now? What is causing the error, and how can I fix it?

Sub save_and_print()

Application.ScreenUpdating = False

Dim logName As String
Dim masterName As String
logName = ActiveSheet.Name
If logName = "Hospital Outcome Log" Then
    masterName = "Hospital Log Master"
ElseIf logName = "On Call Log" Then
    masterName = "On Call Master"
Else
    masterName = "MHC Master"
End If


'Check to see that the two required fields are entered.

Sheets(logName).Range("B5").Interior.Color = RGB(217, 225, 242)
Sheets(logName).Range("E5").Interior.Color = RGB(217, 225, 242)

 

[Candyman8019]

A few things I would check:
- I would confirm that you have the correct spelling of each of your sheets, ie Hospital Outcome Log; On Call Log
- Confirm there are no leading or trailing spaces in the sheet names.

 

[mhessnm]

A few things I would check:
- I would confirm that you have the correct spelling of each of your sheets, ie Hospital Outcome Log; On Call Log
- Confirm there are no leading or trailing spaces in the sheet names.

Thank you, Candyman...I did that and the sheet names are correct with no trailing or leading spaces.

 

[Candyman8019]

Your code seems to run just fine on my end. I have had situations where Excel just isn't functioning as expected and a reboot has cleared things up. Maybe give that a try.

 

[mhessnm]

Your code seems to run just fine on my end. I have had situations where Excel just isn't functioning as expected and a reboot has cleared things up. Maybe give that a try.

Okay, I was thinking that because there is no reason why it shouldn't work when it did the other day. Thanks!

 

[abdelfattah]

Sheets(logName).Range("E5").Interior.Color = RGB(217, 225, 242)

this is wrong to sheet reference !
where is quotation ?
should be like this Sheets("logName")

 

[mhessnm]

Sheets(logName).Range("E5").Interior.Color = RGB(217, 225, 242)

this is wrong to sheet reference !
where is quotation ?
should be like this Sheets("logName")

Hi Abdelfattah, I think when you are referencing a variable referring to a string, you do not need to use the quotations. If I were directly referencing the sheet, I would need to use the quotations. At least that is my understanding.

 

[mhessnm]

Your code seems to run just fine on my end. I have had situations where Excel just isn't functioning as expected and a reboot has cleared things up. Maybe give that a try.

Hi Candyman...I've found the problem, though not sure what is causing it. We have a directory on OneDrive where I have loaded the file. When it hits that RGB command when running from that file, it hangs up. When I run the code using the file from my OneDrive directory, it works. I don't reference a drive anywhere in the code, and not sure why it would hang up on that one command that has nothing to do with the drive (and only in that drive), but there you go.

 

[Candyman8019]

The OneDrive location where the file is stored...could that be read-only for you? Or do you have full access to it?

 

[mhessnm]

The OneDrive location where the file is stored...could that be read-only for you? Or do you have full access to it?

Full access...but I think I know the problem and will test. It's one of those d'oh moments where you are embarrassed to admit it. I need to remove protection from the sheets. I didn't have protection on the sheets I tested. And so it hung up when I asked it to change a cell with protection.

God, how stupid, SMH.