Save data to sheet selected from Combox

Damocles2021

New Member
Joined
Mar 6, 2021
Messages
4
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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! (y))
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....
VBA Code:
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,

VBA Code:
.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
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,388
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
Set ws = Sheets(Myfrm)
With ws
etc...
or you could just use:

VBA Code:
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
VBA Code:
With Sheets(Myfrm)
.Range etc
.Rows etc
.Cells etc
End with
 

Damocles2021

New Member
Joined
Mar 6, 2021
Messages
4
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.

VBA Code:
Set ws = Sheets(Myfrm)
With ws
etc...
or you could just use:

VBA Code:
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
VBA Code:
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. :confused:
(I changed 'With Myfrm' to 'Set ws = Sheets(Myfrm)' & placed 'With Sheets(Myfrm)' before the .Cells statement)
 

Watch MrExcel Video

Forum statistics

Threads
1,129,383
Messages
5,635,942
Members
416,889
Latest member
dhegs

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top