Save data to sheet selected from Combox

[Damocles2021]

Hi All
I'm currently wanting to populate a sheet chosen from a combobox on Userform1
I have 7 worksheets named Room 1, Room 2 etc, all the sheets have Appointment times in Column A2 - A28 and Dates across the rows B2 - AF1 (31 days)
The userform has 3 comboboxes, (txtRoom, cboName, cboTime) and 1 Textbox, (txtDate). 1 command button, cmdSave
I'm trying to adapt some code that @mumps provided in an early query, (Thank you very much, works like a charm! )
What I want to achieve is User selects a Room, Time and name from dropdowns and inputs the date and clicks Save, which would transfer the data into the selected ROOM sheet

What I have so far....

Private Sub cmdSave_Click()
Dim aptTime As Range, aptDate As Range
Myfrm = UserForm1.cboRoom.Value

With Myfrm
Set aptDate = Range("A:A").Find(txtDate.Value, LookIn:=xlValues, lookat:=xlWhole)
Set aptTime = Rows(1).Find(cboTime.Value, LookIn:=xlValues, lookat:=xlWhole)

.Cells(aptDate.Row, aptTime.Column) = cboName.Value
End With
End Sub

I have played with the code in all sorts of different ways, been hit with the excel vba object variable or with block variable not set,

.Cells(aptDate.Row, aptTime.Column) = cboName.Value

When I go through the 'debug' and hover over the code where data should be it shows the correct entries in the form controls, all except the line above.
The thing is I know I'm missing something thats probably staring me in the face, or gone through my mind, I believe (could be wrong)its the way Im referencing that line of code that shows the error. Any help appreciated, although I would like to try and solve it myself (best way to learn ) but been at it for ages so now to hand over to the experts!
Thanks in advance

 

[steve the fish]

Ok you have some problems there. Im going to presume Myfrm contains the sheet name?? To then tell the code you mean the sheet you need to Set.

Set ws = Sheets(Myfrm)
With ws
etc...

or you could just use:

With Sheets(Myfrm)
etc...

Now within the With statement you then use Range and Rows. You are not using the sheet you think you are. If you dont qualify the Range with a sheet then it uses Activesheet. now because you are using a With statement you can just use .Range or .Rows. The dot is read by the code as use whatever i have been told by the With.

So you now have

With Sheets(Myfrm)
.Range etc
.Rows etc
.Cells etc
End with

 

[Damocles2021]

Ok you have some problems there. Im going to presume Myfrm contains the sheet name?? To then tell the code you mean the sheet you need to Set.

Set ws = Sheets(Myfrm)
With ws
etc...

or you could just use:

With Sheets(Myfrm)
etc...

Now within the With statement you then use Range and Rows. You are not using the sheet you think you are. If you dont qualify the Range with a sheet then it uses Activesheet. now because you are using a With statement you can just use .Range or .Rows. The dot is read by the code as use whatever i have been told by the With.

So you now have

With Sheets(Myfrm)
.Range etc
.Rows etc
.Cells etc
End with

Hi Mr the Fish, Thanks for replying, I did try that before asking for help, as I understood the way too use 'With...... End With' statement, but thought I may have missed something, However I still get the same error in the same place.
(I changed 'With Myfrm' to 'Set ws = Sheets(Myfrm)' & placed 'With Sheets(Myfrm)' before the .Cells statement)