Hey Guys...
I have created a user form where the user inputs sales data for certain locations. The data that is inputed is then displayed into certain worksheets depending on the values.
For example, the user form contains a combo box where user can select weeks 1 - 4, another combo box where he can select the location, a 'total revenue' txt box, 'gop' text box, and rooms sold text box.
depending on which week the user selects, i want the data to be input into that specified worksheet. For example, submitting data for week 2 at Hotel A, will input data into worksheet two and in the Hotel A row.
I tried using Select Case, but I am running into some problems.
I have attached the sample worksheet for your review.
Also, below is the code I have written so far. Help would be appreciated. Thanks!
Private Sub UserForm_Initialize()
With cboWeek
.AddItem "Week 1"
.AddItem "Week 2"
.AddItem "Week 3"
.AddItem "Week 4"
End With
cboWeek.Value = ""
With cboHotel
.AddItem "Hotel A"
.AddItem "Hotel B"
.AddItem "Hotel C"
.AddItem "Hotel D"
.AddItem "Hotel E"
.AddItem "Hotel F"
.AddItem "Hotel G"
End With
cboHotel.Value = ""
txtRoomRevenue.Value = ""
txtTotalRevenue.Value = ""
txtGOP.Value = ""
txtRoomSold.Value = ""
cboWeek.SetFocus
End Sub
Private Sub cmdOK_Click()
Select Case cboWeek
Case "Week 1"
ActiveWorkbook.Sheets("Week 1").Activate
Select Case cboHotel
Case "Hotel A"
ActiveCell(6, 3).Select
ActiveCell.Value = txtRoomRevenue
ActiveCell.Offset(0, 1) = txtTotalRevenue
ActiveCell.Offset(0, 1) = txtGOP
ActiveCell.Offset(0, 2) = txtRoomSold
Case "Hotel B"
ActiveCell(7, 3).Select
ActiveCell.Value = txtRoomRevenue
ActiveCell.Offset(0, 1) = txtTotalRevenue
ActiveCell.Offset(0, 1) = txtGOP
ActiveCell.Offset(0, 2) = txtRoomSold
Case "Hotel C"
ActiveCell(8, 3).Select
ActiveCell.Value = txtRoomRevenue
ActiveCell.Offset(0, 1) = txtTotalRevenue
ActiveCell.Offset(0, 1) = txtGOP
ActiveCell.Offset(0, 2) = txtRoomSold
Case "Hotel D"
ActiveCell(9, 3).Select
ActiveCell.Value = txtRoomRevenue
ActiveCell.Offset(0, 1) = txtTotalRevenue
ActiveCell.Offset(0, 1) = txtGOP
ActiveCell.Offset(0, 2) = txtRoomSold
Case "Hotel E"
ActiveCell(10, 3).Select
ActiveCell.Value = txtRoomRevenue
ActiveCell.Offset(0, 1) = txtTotalRevenue
ActiveCell.Offset(0, 1) = txtGOP
ActiveCell.Offset(0, 2) = txtRoomSold
Case "Hotel F"
ActiveCell(11, 3).Select
ActiveCell.Value = txtRoomRevenue
ActiveCell.Offset(0, 1) = txtTotalRevenue
ActiveCell.Offset(0, 1) = txtGOP
ActiveCell.Offset(0, 2) = txtRoomSold
Case "Hotel G"
ActiveCell(12, 3).Select
ActiveCell.Value = txtRoomRevenue
ActiveCell.Offset(0, 1) = txtTotalRevenue
ActiveCell.Offset(0, 1) = txtGOP
ActiveCell.Offset(0, 2) = txtRoomSold
End Select
Case "Week 2"
ActiveWorkbook.Sheets("Week 2").Activate
Case "Week 3"
ActiveWorkbook.Sheets("Week 3").Activate
Case "Week 4"
ActiveWorkbook.Sheets("Week 4").Activate
End Select
End Sub
Bookmarks