Ark68
Well-known Member
- Joined
- Mar 23, 2004
- Messages
- 4,564
- Office Version
- 365
- 2016
- Platform
- Windows
I have a question, which is likely of a basic understanding of how Excel works.
I am working on a VBA project.
From worksheet one, the user initiates VBA code (through a macro enabled shape). Part of this code changes the formatting of a range of cells on another worksheet (ws_master).
The line in red is giving me the error "Method 'Range of object '_Worksheet' failed." error.
This does not happen if I step through the code with worksheet ws_master visible, ie I switch sheets from worksheet one to ws_master. If I step through the code with sheet one visible, I get the error.
What must I add to my code, or change in my code, to avoid having to "activate"(?) ws_master first? I'd prefer all of this to happen behind the scenes from worksheet one. I don't want the user to be able to see ws_master, so not only do I want to take focus of it in the procedure, I also want to hide it eventually.
I am working on a VBA project.
From worksheet one, the user initiates VBA code (through a macro enabled shape). Part of this code changes the formatting of a range of cells on another worksheet (ws_master).
Rich (BB code):
Sub GUI_Chg_Submit2()
ui1 = MsgBox("This commits the current schedule for " & Format(n_date, "dddd mmm-dd") & Chr(13) & "to the master schedule after accuracy has been checked." & Chr(13) & Chr(13) & "Do you wish to continue?", vbQuestion + vbYesNo, "COMMITMENT")
If ui1 = vbNo Then Exit Sub
'transfer to master
With ws_master
'find date row
Stop
.Unprotect
... some code
.Range(Cells(d_row, bcn), Cells(d_row, bcn + 4)).Font.Color = RGB(226, 107, 10)
... more code
End With
End Sub
The line in red is giving me the error "Method 'Range of object '_Worksheet' failed." error.
This does not happen if I step through the code with worksheet ws_master visible, ie I switch sheets from worksheet one to ws_master. If I step through the code with sheet one visible, I get the error.
What must I add to my code, or change in my code, to avoid having to "activate"(?) ws_master first? I'd prefer all of this to happen behind the scenes from worksheet one. I don't want the user to be able to see ws_master, so not only do I want to take focus of it in the procedure, I also want to hide it eventually.