Bushwalking Route Card

[Dave T]

Hello All,

When I go bushwalking I fill out a route card to plan my distances and walking times.
The route card is based on Naismith’s rule (http://en.wikipedia.org/wiki/Naismith's_Rule).

I am wondering if the attached Excel 2003 workbook can be recreated in Word 2003. I suspect it will require the use of VBA to do the required calculations as the formulas will need to be added to each new row as it is created. No grand totals are required as it is just a day by day planning tool.
A copy of the completed route card is left with various people as a safety measure.

In an attempt to simplify my planning and at times my handwriting can be a bit hard to read I created an Excel spreadsheet to work out my times.

I use the sheet as follows:

Cell F1 is the 1:50,000 map sheet name.
Cells M1 and O1 are the start and end dates for the hike.
Cell C2 lists the emergency contact names and phone numbers.
Cell O3 is the declination (plus or minus) from the 1:50,000 map sheet.
Cells A6 down are just a copy of the cell to the right and one row above (i.e. cell A6 is copy of B5). I did this just to save time as the grid reference for the end of the previous leg will be the starting grid reference of the next leg.
The ‘True’ bearing (column C) of the leg is taken off the map.
The formula in the column D works out the ‘Magnetic’ bearing, which is used with the compass when walking.
Column E is the walking distance for the leg.
Details of height gained and/or lost in columns F & G are taken from the map contours.
Column H is where Naismith’s rule is applied to the data in columns E, F & G.
The formula in column I works out the estimated walking time.
When I have worked out what is a reasonable days walking I leave a blank row and start planning the next days walking.The problem I have is that unlike Word, Excel does not allow you to have a footer that repeats on the bottom of each page. Hence the Excel worksheet is set up with page breaks after a 'repeated' footer rows.

If the ‘route card’ could be created in Word rows 1 to 4 could be set up as a table in the header and the last three rows could be set up as a footer.
The only item on the page would be a table where the data is entered.

Being relatively new to Word VBA can anyone advise me if this is possible and how it could be done or even whether I should bother trying to do this in Word and just stick to Excel and live with adding or deleting rows to enable a type of header and footer on each page.

Regards,
Dave T

[Dave T]

Hello again,

I wanted to include the Word version basic layout to show what I was trying to do.

Regards,
Dave T

[Tinbendr]

This is my effort.

When working with formfields, I prefer to stick to a fixed form. If you start adding and deleting formfields, it can get messy real quick. Since this form will be doing just that, I decided to use a userform to collect information.

As far as deleting is concerned, I've left that a manual task. (Maybe a later project)

Since you didn't upload any real data with your chart, you'll have to check the math.

You may want to add a quick button to the menu for easy use.

[Dave T]

Hello Tinbendr,

A truly amazing effort. I did not really want to use FormFields but it was the only/easiest way to show what I was trying to do.
Your reply/solution is a much better way of doing what I was after and is exactly what I wanted to do but I had no idea as to how to do it.

Having had a play with your version of the route card here are some observations I have found:

The fixed form closes each time the 'Add' button is clicked. Is there a setting that needs to be or can be changed to allow the form to stay open and just move data entry to the next row down after the add button is clicked. When you have finished entering all data the 'Exit' button is the only time the form needs to be closed.
Within the text box for the 'Description of Ground' I needed to set the MultiLine property to true so that as text is entered in the text box the text wrapped within the box instead of scrolling out of view within the text box.
The declination (which you referred to as deviation) is a fixed value (+ve or -ve) per map sheet, so is it possible to just enter this once and this value is reused for each subsequent entry? I was going to suggest if the declination value could be taken from D5 in the header table, but then I thought if you needed to use another map sheet this value may be different and need to be changed. So is it possible to have the form reuse the last declination value entered?
I originally used the mouse to move between the data entry text boxes and nothing happened. I found out by accident that if you tabbed between text boxes the magnetic text box was automatically updated with a calculated value as is the same for the estimated calculated walking times. Is it possible to have the calculations occur and be displayed when you mouse click in another text box?
As the final result of the 'Magnetic' text box is just a calculation derived from the 'Declination' and 'True' text boxes can the result under the 'Magnetic' heading just show the calculated result and not an editable text box; then the value returned is added to the appropriate table column in the word document?
I have been trying to get my head around your calculations, so I have included some worked examples in another version of your Word example and included a copy of a previously used Excel worksheet (page 3 has the various example calculations).If the map has a declination of +5 degrees between true/grid north and magnetic north the values should be:
350 degrees grid/true would be 345 degrees magnetic, 355 degrees grid would be 350 degree magnetic; 360 degress grid would be 355 degress magnetic; 5 degrees grid would be 360 degrees magnetic; 10 degress grid would be 5 degress magnetic.
Conversley..
If the map has a declination of -5 degrees between grid/true north and magnetic north the values should be:
350 degrees grid/true would be 355 degrees magnetic, 355 degrees grid would be 360 degree magnetic; 360 degress grid would be 5 degress magnetic; 5 degrees grid would be 10 degrees magnetic; 10 degress grid would be 15 degress magnetic.
For some reason if the map sheet has a declination of -5 degrees and a Grid/True north value of 360 degrees the 'Magnetic' text box returns a value of -5 degress and this should be a +ve value of 5 degrees (i.e. there would never be a negative value). 360 - 360 - -5 = 5 degrees.

The final 'Calculated Time' formula will need some work to, as if I use some set figures to test, for which I know the expected result:
If you are going to walk 10 kilometres at 1km/h this will take you 10 hours. If you ascend a cumulative 500m in that 10 kilometres you add another hour. If you descend a cumulative 1000 metres in that 10 kilometres you add another hour.
TOTAL Time = 10:00 hours + 1:00 hour + 1:00 hour = 12:00 hours estimated walking time. For some reason if I use these figures in the Word form the 'Calculated Time' text box returns 11:04 hours.

I really can't thank you enough Tinbendr for taking the effort and time to have a go at Route card and relpy to my question.

Regards,
Dave T

[Dave T]

Excel Workbook referred to in previous message.

Regards,
Dave T

[Bob Phillips]

Dave,

Is the problem just adding the footer to each printed page? If so, that could be easily handled in VBA.

[Tinbendr]

Thanks!

1. I had a problem with keeping the userform open and the calculation auto-updating. That's why the one entry per userform. I've discovered that there's a flaw in VBA, but I think I've found a workaround.

2. Ok, I'll fix that.

3. (I did change it to deviation. Sorry, I work in aviation and we call it deviation.) With the new version, I do leave the deviation unchanged as long as the userform is open.. I can also give it a start value if you like.

4. Ugh, the mouse. I hate using it to move between fields. Completely inefficient in my opinion, but I'll look into that as well.

5. I can change the Magnetic field to a label, so it can't be edited.

6. The calculations were straight from the Excel sheet. I'll look at the new one you uploaded and bump it against the Word version.

It may be a few days before I can get back to this.

[Dave T]

Hello Tinbendr,

Having read your reply it did occur to me that the text box that calculates the final walking time should also be a label.
I am still very impresed with what you have done so far.


Hello XLD,

You said
...is the problem just adding the footer to each printed page? If so, that could be easily handled in VBA.

I am curious what you are proposing... Are you talking about using VBA within Excel 2003 to add the footer to each page or Word 2003.

I did look at the option of creating a picture and adding this within the Excel footer, but this meant I was no longer able to edit it any more, hence I posted the question about a Word version of the route card.

I am just curious as to the different ways to create a route card.

Regards,
Dave T

[Bob Phillips]

My suggestion is to have the header and footer on a separate sheet, and have the ROUTE CARD sheet for just entering the walk/day details. You manually insert the page breaks, and then a print button brings it all together.

Just seems easier to amend Excel as that works already.

I will knock something up to show you.

[Bob Phillips]

Here's my suggestion

[Dave T]

Hello XLD,

Gee what an interesting approach and yes it does do what I was after.
When I use the print button the printed file is smaller on the page. I noted you set it to print one page wide by one page high (I assume this is because a specific number of rows have been set to be copied and the fit setting reduces the width accordingly).
I would like to have a go at reducing the number of rows copied to the 'Scratch' worksheet to enable the full width of the page to be used. Can you please tell me which lines of code I should either delete or just comment out to enable this to be done.

Also...
The three rows that are used as the footer when printed are much higher than the source data. Is there a way of controlling how high the footer rows on the scratch sheet are when data is copied to it ???


Hello Tinbendr,

I have been playing some more with your word version and know why the Estimated Time calculation is wrong. I made a mistake with my formula that you used:

((E11/H1)+(F11/500)+(G111/1000)/24)
When I tested various combinations e.g. 10 kms at 1km/h + 500 metres up the result was correct i.e. 11 hours.
The error occured when I tried 10 kms at 1km/h + 1000 metres down. The answer should have been 11 hours, but the divide by 24 was only being applied to the last part of the calculation.
When I changed the formula to the following I was now getting a decimal time of the correct answer:

((E11/H1)+(F11/500)+(G111/1000))
i.e. 10 kms at 1km/h + 500metres up + 1000 metres down = 12 hours
I have been trying to convert the decimal time result to a hours and minutes time format h:mm by using the following changes to your code, however I am still not getting a correct result:

.txtCalcTime = Format(((.txtDistance / .cmbEstSpeed) + (.txtHeightGained / 500) + (.txtHeightLost / 1000)) / 1440, "HH:mm")
I haven't yet tried to work out the bearing problem yet or any other the other things I raised.

Regards,
Dave T

[Dave T]

Sorry wrong file

[Tinbendr]

I have been working on it.

I tried to mimic the End of Day function, but it's not working correctly.

Here's the latest version.

I fixed the Tab only calculation. Now you can use the mouse.

The reason the calculation uses '/24' is so Word's format function will correctly convert the number into a sensible time. Well, this matches the Excel calulations anyway.

Maybe I can get it finished this weekend. (Work and church have kept me busy this week.)

[Bob Phillips]

Sorry wrong file

Can you post the correct one then?

[Dave T]

Hello XLD,

The Word document posted in reply number 11 was essentially the correct one, but the formula used to calculate the time was the incorrect one. I was playing with various options for the formula and forgot to use the formula that gives the corect time but this is in decimal hours.

.txtCalcTime = Format(((.txtDistance / .cmbEstSpeed) + (.txtHeightGained / 500) + (.txtHeightLost / 1000)), "0.00")
Post number 11 was created using my work computer and post number 12 was from my home computer. I thought I would be able to edit the previous post from home, but found out I was unable to do so.

My appologies for any confusion.

I am also curious if you can explain to me the questions I asked in post number 11....


I would like to have a go at reducing the number of rows copied to the 'Scratch' worksheet to enable the full width of the page to be used. Can you please tell me which lines of code I should either delete or just comment out to enable this to be done.
Also...
The three rows that are used as the footer when printed are much higher than the source data. Is there a way of controlling how high the footer rows on the scratch sheet are when data is copied to it ???


Regards,
Dave T

[Dave T]

Hello Tinbendr,

Once again you amaze me. You have a good attention to detail.
I had made minor 'cosmetic' changes to the Excel version to suit my needs, so it was a surprise to see you had incorporated some of these into your latest version.

Within Excel it was easy to create a custom number format for the grid reference to be entered as a 6 figure number yet be shown as 3 numbers followed by a space then the last 3 numbers. I tried, without success, to work out if this could be achieved in Word and you have done it.Others changes you have made are also very good:

The redesigned slimmer input form is much better as it can be parked to one side and as the data is entered into the table it is still visible.
The caption labels for the ‘Magnetic’ and ‘Calculated Time’ calculations is very good.
The ReCalculate button was a very nice addition and something I had not even thought of (Excel does this automatically).
Good to see the ‘Add’ button no longer closes the form.
The ‘EndDay’ button is a nice touch if you can get it to work, but not essential, and I have previously just done this manually. The total estimated time is useful when planning walks with groups of different fitness levels or ages (I have found that around 6 hours walking time is about right for a reasonably fit adult group).I have also noted a few other things:

In the version I posted in reply number 11, I split the cell in the header for the declination. Having worked with bookmarks before I know it can be easy to accidently delete them. So I thought if the declination value was in its own cell to the right of the word ‘Map Declination’ it could not be easily deleted as the code would just be referring to a specific cell in a header table.
The ‘Add’ button does not add the data to the table when clicked. It was only when I clicked the ‘Exit’ button was the data added to the table. The ‘Add’ button looks like it saves/stores the data and only when the ‘Exit’ button is clicked does it add the saved/stored data to the table.Once again thank you for all your help

Regards,
Dave T

[Bob Phillips]

The Word document posted in reply number 11 was essentially the correct one, but the formula used to calculate the time was the incorrect one. I was playing with various options for the formula and forgot to use the formula that gives the corect time but this is in decimal hours.

.txtCalcTime = Format(((.txtDistance / .cmbEstSpeed) + (.txtHeightGained / 500) + (.txtHeightLost / 1000)), "0.00")
Post number 11 was created using my work computer and post number 12 was from my home computer. I thought I would be able to edit the previous post from home, but found out I was unable to do so.


I can't see what that has to do with the Excel version?


I am also curious if you can explain to me the questions I asked in post number 11....

I have tweaked the code so that it sets the print area explicitly rather than allowing Excel to default.

You tweak the printout by changing the page breaks. Originally I had page breaks after day4 and after day 7, but I have also now inserted one after day 6 to show you what happens. Play with it and see.

This is a far less complex solution than Word as Excel does all the heavy lifting.

[Dave T]

Hello xld,

Thanks again for you help and yes it works well.

Funny how at time you can't see the wood from the trees... I was looking at your code and could not see the page break reference, then I looked at your file using Page Beak Preview and everything you mentioned was suddenly clear.

I had spent quite a lot of time searching the web to see if there was a solution to adding a footer to an Excel worksheet without success, but your solutions enables me to do this.


I can't see what that has to do with the Excel version?
I did not actually refer to specific file type. I just posted an older version of a 'Word' file and then found out I could not edit my post from a different computer. Maybe I mistakenly thought the description 'file' was not program specific.


I really do appreciate all of your help and comments.

Regards,
Dave T

[Bob Phillips]

Well hopefully you have a working solution now Dave.

I must admit I was intrigued by this one. I like to go on hikes in the UK, the thought of going of week long bush-walks seems somewhat less easy to me :)

[Dave T]

Hello Tinbendr,

I just have one more question for you…

How to you refer to a specific cell within a table that has been placed in a Word document page header ???

The reason I ask is that in the version of the Word document I posted in reply number 13, I have split the table cell in the header for the declination so that the declination value will no longer be a bookmark, but will reside in a cell all by itself.

Having created forms for use by other people I know how easy it can be to accidently delete bookmarks. So I thought if the declination value was in its own cell to the right of the word ‘Map Declination’ it could not be easily deleted as the code would just be referring to a value within a specific cell in a header table.

Both you and XLD have provided me with great solutions, but I would like to see how to make these changes to the Word version. The Word version seems so close but not quite yet finished.

Regards,
Dave T

[Tinbendr]

My apologies on my tardiness. I decided to let this incubate a bit. You know the saying, Out of sight, out of mind.

To access the table is a bit long winded.
ActiveDocument.Sections(1).Headers(wdHeaderFooterPrimary).Range.Tables(1).C ell(3, 3).Range.Text will get you the text. You can parse it from there.

This might be a good place for a content control. Accessing merged cells in tables normally don't play well and since the content control has a setting preventing it from being deleted, this might be a better approach.

To answer your previous question about the table not being updated after entering the data, this is normal. The screen refresh is off while userforms have the focus. Simply add Application.ScreenRefresh at the end of the cmdAdd_Click() sub to force a refresh.

My problem with End Day is adding the times. I haven't investigated how to accurately add the times for the day. Using Word's formula =Sum(Above) returns a nonsense answer. (But that won't work anyway since it will keep adding the times for the whole table.)

David

[Dave T]

Hello Tinbendr,

Thanks for the hint about refering to a table cell within the header. I used the macro recorder to see how this worked, but it came up with quite a lot of extra code. I knew there was a much simpler way to do it but did not know how to do it or trim what the recoder had produced.

I did split the Declination cell into two and removed the bookmark, but every time I opened the form all the VBA code was deleted. If split the cell into two and left the bookmark next to the text all was fine, but deleting the bookmark caused problems.

I also did wonder how the SUM above was going to work as there were no blank rows to separate the calculations. I thought it would work for the first calculation, but then on include all the previous calculations above in subsequent totals.

The End of Day function you added whilst nice is probably not worth the effort. Maybe it and all associated code should be removed.
I personally would just manually insert an End of Day comment and manually tally up the times.

Thanks again for you help and advice. It has been good to look at you code and try to follow what you have done.

Regards,
Dave T

[fumei]

If you need to consistently, or regularly, refer to a table - regardless of where it is (header, footer...whatever) - then bookmark the table, and then use a table object. It does not matter what Section it is in, nor which header type.

If the table in the header is bookmarked (named as HeaderTable)

Sub TableInHeader()
Dim oTable As Table
Set oTable = ActiveDocument.Bookmarks("HeaderTable").Range.Tables(1)
oTable.Cell(3, 3).Range.Text = "yadda"
End Sub


OR...

if the cell in the table header is itself bookmarked, then:
ActiveDocument.Bookmarks("InsideTheheaderTable").Range.Text = "blah"


As it likely needs to be a repeatable action, you want to insert the text into the bookmark (without deleting the bookmark).

Sub FillBM(strBM As String, strText As String)
Dim r As Range
Set r = ActiveDocument.Bookmarks(strBM).Range
r.Text = strText
ActiveDocument.Bookmarks.Add Name:=strBM, Range:=r
End Sub

Sub TextIntoTableHeaderBookmark()
Call FillBM("InsideTheheaderTable", "your text")
End Sub


A bookmark will work, even inside a merged cell.

[Dave T]

Hello Gerry,

Thanks very much for the examples.

I have trying to incorporate them into Tinbendr's latest version to see how they work.

I have learnt so much more about the finer points of Word.

Regards,
Dave T

[Dave T]

Hello Tinbendr & Gerry,

I can’t thank you both enough for your help and suggestions with this post.

Just thought I'd share with you and others where I have finally taken the Word version of the Route Card.

I am still playing with (getting my head around) the suggestions from Gerry in post number 23 and how to implement them, just to see how they would work.

The word version of the Route Card may still get further refinements as what I have done probably could be done better.

I have:

Removed the ‘End of day’ feature (easier to manually add these up an add totals to row if and when required)
Slimmed down the UserForm even more and used code from this site to control where on the screen the UserForm opens
Split the table cells in the header and used the code suggested by Tinbendr to no longer refer to a bookmark but a specific cell in the header table
Once again thanks for all your help and if you have any further comments they would be appreciated.

Regards,
Dave T