Golf Club Sign-In Sheets 2

[JPJeffery]

Hello Tek-Tippers!

LTNS!

My golf club (like most, if not all, other golf clubs) runs monthly competitions called 'Monthly Medals'. This competition is open for entry over three days, usually the Friday, Saturday, and Sunday of the second full weekend of the month (so 7th-9th at the earliest, 14th to 16th at the latest).

The Saturday of the this competition weekend is the official day, with the Friday and Sunday designated as 'Alternate' days. Sometimes (rarely) the course is closed on a Friday so we move that alternate day to the Monday (but this is rare enough that we can deal with it manually as and when required).

Each member can only play in the competition once, and to declare this they have to sign in on a sheet before they tee off (if they play on the Friday and have a bad round, or they get rained off, it's tough luck, they can't play on a different day to submit a second scorecard).

So, every month we open a Word (2016) template to create a PDF of the sign in sheet to supply to the pro shop to print off and place on the board. This sheet has three pages, one page for each day. Each page states the following information which changes (or potentially can change) every month:
[ul]
[li]The name of the competition (e.g. "Men's January 2020 Monthly Medal")[/li]
[li]The date for each day[/li]
[li]Whether it's a Handicap Qualifier (this is a boolean)[/li]
[li]The scoring format (essentially another boolean as there are only two choices: Stableford and Strokeplay)[/li]
[li]The tees to be used[/li]
[li]Are Winter Rules in use (another boolean)[/li]
[/ul]

Here's an example screenshot (with some added highlights):

Up until now we've manually changed the various parts (usually with Find Replace), but sometimes the human factor gets involved, for instance supplying a PDF that stated the wrong scoring format, or that winter rules were in operation in August!

I've been playing around with Content Controls, which can be nice, but still require the operator to manually change the value of each one. Not cool. What I'd prefer is to run a VBA macro to prompt the operator for each bit of information as per the bullet list above.

Clearly we only want to enter one date (once) then let differently formatted bookmarks display either the Month and Year, or the day, or the full date as required, adding a day on page two, then another day on page three (page 3 not shown in the screenshot example).

So far I've got this code:

Sub SetDocVars()
    On Error Resume Next
    strScoringFormat = "Strokeplay / Stableford"
    strTee = "All tee shots must be taken from the white teeing areas. Use the white markers if available AND in usual white tee box, otherwise UP TO five club lengths forward of the white plates."
    strWinterRules = "Winter Rules (preferred lies on closely mown grass)"
    strScoringFormat = InputBox("Which scoring format is to be played (delete as appropriate)?", "Scoring Format", strScoringFormat)
    strTee = InputBox("Which tees are to be used?", "Tees", strTee)
    
    ActiveDocument.Variables.Add Name:="ScoringFormat", Value:=strScoringFormat
    ActiveDocument.Variables.Add Name:="StartDate", Value:=dtStartDate
    ActiveDocument.Variables.Add Name:="HandicapQualifier", Value:=bHandicapQualifier
    ActiveDocument.Variables.Add Name:="Tee", Value:=strTee
    ActiveDocument.Variables.Add Name:="WinterRules", Value:=bWinterRules
    
    ' Select everything to update the fields (because Microsoft still assume everything is printed so only auto-update fields at print time)
    Dim myRange
     
    Set myRange = Selection.Range
     
    Selection.WholeStory
    Selection.Fields.Update
     
    myRange.Select
    
    ' Delete the variable.
    ActiveDocument.Variables("ScoringFormat").Delete
    ActiveDocument.Variables("StartDate").Delete
    ActiveDocument.Variables("HandicapQualifier").Delete
    ActiveDocument.Variables("Tee").Delete
End Sub

But this approach of adding then deleting document variables seems very clunky. Also I've not yet considered how to get the date from the operator. A DatePicker would be nice but I'm not yet convinced VBA is up to that (likely that I'm wrong though).

So, all a bit big and vague. Sorry, but still, what are your thoughts?

Urgency level is low.

JJ
[small][purple]Variables won't. Constants aren't[/purple]
There is no apostrophe in the plural of PC (or PST, or CPU, or HDD, or FDD, or photo, or breakfast...and so on)[/small]

 

[SkipVought]

Hi,

I'd be apt to use Excel rather than Word.

In Excel you can store lists that can be used to make Drop Down Selections for various parts of the Scorecard, like the binary selections you referred to.

The template can either be three separate sheets, or one sheet printed for any of the three days. A question is raised pertaining to procedure. Do you pre-print a fixed number of scoresheets or is each scoresheet printed on the day of competition or something else?

I'd opt for ONE SHEET. Seems to me that the amount of coding would be minimal. Most functionality would be in the sheet using the Data > Validation > List feature.

When you refer to "Document variables" this really refers to areas of the template that need to be filled in prior to printing, yes? In Excel, those areas (cells) could be Named permanently and filled variably, with the proper competition name or format or date or... That's where the List feature comes in handy.

Probably need to know more about what things change from month to month or season to season. The key to keeping it simple is to put DATA into lists rather than into code. Lists are easy to change. Changing code is a pain!



Skip,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[Andrzejek]

Disclaimer: I don’t know anything about golf.

I do something similar, but the data comes from the data base and fills a Word template. You can use the same approach.

Create a Word Macro Enabled Template (*.dotm) file with 3 pages where all ‘changeable’ text is replaced by bookmarks which would be already formatted with the proper font, size, color, etc. Have a UserForm as an input form where you can collect all info necessary to create your 3 sign-up sheets.

Start Date may be set by a DatePicker where you can control that only Friday (or Saturday) could be selected. You already know that the competition will last 3 days, so it easy to calculate next 3 days from Start Date. If Friday is selected as a Start Date, Friday and Sunday will be the 'Alternate' days, if Saturday is selected as a Start Date, Sunday and Monday will be the 'Alternate' days.

Start Date’s month will be the ‘driving force’ in the name of the competition. So, if January will be the Start Date’s month and ‘Monthly Medal’ is always in the name, then you just need a text box for entering “Men’s” to get "Men's January 2020 Monthly Medal"

2 check boxes for ‘Handicap Qualifier’ and ‘Winter Rules in use’. The latter may be checked and grayed-out if the Start Date is between October and March, un-checked and grayed-out for May thru July, and available to check of unchecked for the remaining months. (Just a guess here…)

And once you have all the necessary data available on your input form, run a little code in ‘Create Sign-In Sheets’ button to replace bookmarks with the data from your form.

And if you name your Bookmarks appropriately (ex. bkmName1, bkmName2, bkmName3 to display the same info on 3 pages, you may just code:

Dim i As Integer
...
With ActiveDocument
  For i = 1 To 3
    ...
    .Bookmarks("bkmName" & i).Range.Text = txtName.Text & " " & lblName.Caption
    ...
  Next i
End With

---- Andy

There is a great need for a sarcasm font.

 

[JPJeffery]

Firstly, apologies for not responding sooner, and thanks for the responses received so far.

I'd be apt to use Excel rather than Word.

Well, I'm certainly a lot more confident with VBA in Excel than anywhere else, but I guess my concern is getting the final result to fit nicely on three separate A3 sheets (bearing in mind some of the descriptions will be of different lengths so differing numbers of lines).

Do you pre-print a fixed number of scoresheets or is each scoresheet printed on the day of competition or something else?

They're printed a day or two ahead of each comp. We supply them by email to the pro shop who kindly print it and put it up on the board.

***

MORE LATER...BUT THIS AFTERNOON GOT MAD BUSY SO WILL CONTINUE TO RESPOND ANOTHER TIME

JJ
[small][purple]Variables won't. Constants aren't[/purple]
There is no apostrophe in the plural of PC (or PST, or CPU, or HDD, or FDD, or photo, or breakfast...and so on)[/small]