VBA to have user pick option from list.

[ERLoft]

I have several workbooks used in the hospitality industry. One of them takes the number of rooms on the books for future dates and calculates forecast transient pickup based on the prior pickup history. My old version of this workbook would rely on a sample of 6 of each day of the week to calculate an average. However, with the way that hotels can be impacted by seasonal changes, I've added a weighted average sheet, which looks at prior year data and recent data, creating a combined average based on user input weighting.

With the old sheet, the macro listed below is part of the code that pulls this forecast from the Transient Booking Pace workbook and places it in a Yield Forecast workbook, where it's used in conjunction with current on the books numbers to build a (hopefully) accurate forecast of future occupancy levels. However, now that I have a weighted average as well, I'm wanting to insert code to prompt the user to choose whether to use forecast pickup from recent data or from weighted data. This should be stored as a string variable. Each set resides in a different worksheet, so the "With Sheets("Yield Forecast Sheet") would change to referencing the variable that was just stored.

Sub PullForecastPickup()
'This macro pulls the forecast transient pickup from the Transient Booking Pace workbook.

Dim DayOfWeek As String
Dim MonthNumber As Integer
Dim Today As Date
Dim RowNumber As Long
Dim DayGapDate As Integer
Dim FirstDayOfMonth As Date

Today = Date
DayOfWeek = CStr(Format(Today, "DDDD"))
MonthNumber = CStr(Format(Today, "M"))
FirstDayOfMonth = Range("B3").Value
DayGapDate = (FirstDayOfMonth - Today)


Call FindTransientBookingPaceWorkbook

Sheets("Yield Forecast Sheet").Activate

With Sheets("Yield Forecast Sheet")
    For RowNumber = .UsedRange.Rows.Count To 1 Step -1
        If (Range("A" & RowNumber) Like DayOfWeek) Then
            If MonthNumber = CurrentMonthToUpdate Then
                Cells(RowNumber, 2).Activate
                CurrentMonthPickup = Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(0, (LastDayOfMonth - EndHistoryDateToGrab))).Value
                Workbooks(YieldSheet).Sheets(Currentsht).Activate
                    Range(ActiveCell.Offset(11, (EndHistoryDateToGrab)), ActiveCell.Offset(11, (LastDayOfMonth - 1))).Value = CurrentMonthPickup
            ElseIf (MonthNumber + 1) = CurrentMonthToUpdate Then
                Cells(RowNumber, 2).Activate
                Month2Pickup = Range(ActiveCell.Offset(0, (DayGapDate - 1)), ActiveCell.Offset(0, (((DayGapDate - 1) + LastDayOfMonth) - 1))).Value
                Workbooks(YieldSheet).Sheets(Currentsht).Activate
                    Range(ActiveCell.Offset(11, 0), ActiveCell.Offset(11, (LastDayOfMonth - 1))).Value = Month2Pickup
            ElseIf (MonthNumber + 2) = CurrentMonthToUpdate Then
                Cells(RowNumber, 2).Activate
                Month3Pickup = Range(ActiveCell.Offset(0, (DayGapDate - 1)), ActiveCell.Offset(0, (((DayGapDate - 1) + LastDayOfMonth) - 1))).Value
                Workbooks(YieldSheet).Sheets(Currentsht).Activate
                    Range(ActiveCell.Offset(11, 0), ActiveCell.Offset(11, (LastDayOfMonth - 1))).Value = Month3Pickup
            Else
                Sheets("Input Sheet").Activate
                Exit Sub
            End If
        End If
    Next RowNumber
End With

Workbooks(TBP).Activate
Sheets("Input Sheet").Activate
Workbooks(YieldSheet).Activate

End Sub

So this code would go between the Call FindTransientBookingPaceWorkbook and the Sheets("...").Activate I've done quite a bit of searching on this, and have come across some information regarding userforms, comboboxes, picklists, etc. but am still a bit confused as to how to do this. Ideally, at this point in the code, a dialogue box would pop up and as the user, "Do you want to import recent data or weighted data?" with either a radio button list of the options or clickable boxes labeled "Recent" and "Weighted". The selection would then set a new variable - TransientPaceToUse - based on that selection. TransientPaceToUse could either work as a direct name for the worksheet I want to reference, which are currently named "YFS - Recent" and "YFS - Weighted', or it could be an intermediary variable that I'd use to set the worksheet name variable.

Hopefully that's not too confusing!

(It's likely obvious, but there's actually a lot more code involved and a large number of public variables that are declared in another module, but everything is written in smaller Subs like this and I use the Call function to reference back and forth as needed. Seems to make it a lot easier to test out the code that way...)

Thanks in advance for any guidance!

 

[AlphaFrog]

So this code would go between the Call FindTransientBookingPaceWorkbook and the Sheets("...").Activate I've done quite a bit of searching on this, and have come across some information regarding userforms, comboboxes, picklists, etc. but am still a bit confused as to how to do this. Ideally, at this point in the code, a dialogue box would pop up and as the user, "Do you want to import recent data or weighted data?" with either a radio button list of the options or clickable boxes labeled "Recent" and "Weighted". The selection would then set a new variable - TransientPaceToUse - based on that selection. TransientPaceToUse could either work as a direct name for the worksheet I want to reference, which are currently named "YFS - Recent" and "YFS - Weighted', or it could be an intermediary variable that I'd use to set the worksheet name variable.

It sounds like what you want to do is fairly basic. Well within your skill set as best I can tell from your code above.

Declare a public string variable called TransientPaceToUse

Create a userform with these four controls...

• Label1 Its caption is your description to the user of what they are to do.
• OptionButton1 Caption = "Recent", Value = True (it's the default selection)
• OptionButton2 Caption = "Weighted"
• CommandButton1 Caption = "OK"

Put code like this in the UserForm's code module to set the public variable to the user's selection.

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] CommandButton1_Click()
    [color=green]'OK button[/color]
    [color=darkblue]Select[/color] [color=darkblue]Case[/color] [color=darkblue]True[/color]
        [color=darkblue]Case[/color] Me.OptionButton1.Value:  TransientPaceToUse = "YFS - Recent"
        [color=darkblue]Case[/color] Me.OptionButton2.Value:  TransientPaceToUse = "YFS - Weighted"
    [color=darkblue]End[/color] [color=darkblue]Select[/color]
    Unload Me
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

Call your userform with something like this...
UserForm1.Show
...where UserForm1 is the name of the userform.

I'm assuming you know what to do with the variable TransientPaceToUse once it's defined.

Is that what you want?

 

[ERLoft]

Thanks much AlphaFrog! That's an excellent explanation of how to achieve what I'm looking for. I'll write up the code tomorrow and do a test run, but with that help I'm sure I'll be able to nail it.

 

[ERLoft]

Yep, that was exactly it. Thanks again AlphaFrog!