Asking for help...

[B Squared]

I have a worksheet that is used to request flight legs from my aviation department. At the top of the page, the user can enter the total number of legs (1-10) requested for the trip, and that will populate column A with the entered number of legs, and also through VBA, require that certain fields within those requested leg rows be populated before saving. I have been asked to see if it is possible to hide those rows that are not necessary for a given request. Example, I have 10 rows (with borders) available for the maximum of 10 legs requested; if only 2 legs are requested, is there a way to completely hide the remaining 8, thereby reducing the number of pages on which the request will print?

Thank you in advance for any help!

 

[Caleeco]

Hey B Squared,

At a high level, your request is certainly solvable with a combination of Formulae & VBA. Are you able to supply us with some sample data to look at?

If not, I can help with the VBA side of things if you let me know:
- Which cell does the user populate the number of legs (1-10) in?
- What is the range of the output? ie. where is the header row of the table, and where are the rows for all 10 legs? eg: headers C1:J1 & legs C2:J12
- Do you want the existing data to be cleared if the user enters a new number of legs?

Cheers
Caleeco

 

[B Squared]

Excel 2013 64 bit

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	AIRCRAFT REQUISITION FORM
2																Last Saved:		05/05/2018 7:52:44 AM
3																Last Printed:		04/24/2018 8:15:10 AM
4	Aircraft:															Trip Title:		Test
5	Requestor:															Lead Passenger:		Test
6	No. Legs		4													Lead Pax Cell:
7																Rev. No.		2
8
9	FLIGHT ITINERARY
10	Leg No.	Date	Charge TO:	Category							Origin					Destination	Depart	Arrive	Services
11
12	1
13	2
14	3
15	4
16
17
18
19
20
21

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Hi Caleeco! Firstly, thank you VERY KINDLY for your willingness to help...unfortunately my example above lost the cleanliness of my hours of formatting!!
-User populates the leg numbers in C6
-Via in cell Formula, Cells A12:A21 will populate with that number, but of course, it currently leaves the remaining unused rows visible.
Cells D10 and S10 are subheadings for dropdown list columns, while all others are "free text" entry cells with autofit and wrapping (the users will type LOTS of notes)
-If the user, for example, were to begin the form with 4 legs requested, and then later changed to a number of legs that is greater than 4, I would like to retain any data entered for the first 4 legs, if at all possible. Ideally in that scenario, the additional leg rows would then appear for population. Using the same example, if the user needed to reduce the legs below 4, it might be best to have them start a new form using this "Master" and copy/paste any data that is still relevant.

Additional info that may be helpful:
VBA is used to generate a message box (Before Save) to the user if certain data is missing from a row that is eligible based on the cell C6 input (number of legs requested). eg, if 4 legs were requested, and only 3 legs worth of data was completed, there would be a message reminding the user to "Please Complete Leg 4 Info".

Thank you so much for your help!
B Squared

 

[Caleeco]

Hey,

Thanks for the additional information and sample information. I had a go at the VBA side of things, presuming that your formulas/drop downs are all already in place. Might need a bit of tweaking as I'm unsure of how your table is formatted etc.

Sub Hide_Unused_Legs()
Dim NoLegs As Integer, CurrLegs As Integer
Dim i As Long
Dim ws As Worksheet


    Set ws = Sheets("Sheet18") 'Change this to match your sheet name
    NoLegs = ws.Range("C6").Value 'User Entered
    
    'Only accepts legs 1-10
    If NoLegs < 1 Or NoLegs > 10 Then Exit Sub
    
    'Count how many legs in the sheet currently
    CurrLegs = WorksheetFunction.CountA(ws.Range("A12:A21"))
    
    'Clear Data is less legs are needed
    If NoLegs < CurrLegs Then ws.Range("A12:S21").ClearContents
    
    'Output new number of Legs & add the border
    For i = 1 To NoLegs
        ws.Range("A12:S21").Borders.LineStyle = xlNone
        ws.Range("A11").Offset(i).Value = i
    Next i
    ws.Range("A11:S" & i + 10).BorderAround Weight:=xlMedium 'change your line style here to match
    
    'Hide unused rows
    ws.Range("A12:A21").EntireRow.Hidden = False
    If NoLegs < 10 Then ws.Range("A" & 22 - (10 - NoLegs) & ":A21").EntireRow.Hidden = True
End Sub

How it works:

Hope that helps
Caleeco

 

[B Squared]

Caleeco,
Thank you for taking your undoubtedly invaluable time to do this! I apologize for my late reply, I've been out on a trip, back at it today. Your example looks like it may work for me...pardon my ignorance, (I'm a mere pilot, in over my head with this) but where should I paste your hard work in the VBA windows? By the way, love the "ExcelWTF"...my sentiments exactly!

B Squared

 

[Caleeco]

Caleeco,
Thank you for taking your undoubtedly invaluable time to do this! I apologize for my late reply, I've been out on a trip, back at it today. Your example looks like it may work for me...pardon my ignorance, (I'm a mere pilot, in over my head with this) but where should I paste your hard work in the VBA windows? By the way, love the "ExcelWTF"...my sentiments exactly!

B Squared

Hi B Squared,

No problem, I enjoy solving excel problems! Especially VBA! Helps me sharpen my programming skills Ah ok, hope you had a good trip.

- You need to get into the Visual Basic Editor, see how-to here:
https://www.excelwtf.com/learn/visual-basic-editor/

- Then double click the 'ThisWorkbook' object in the Project Explorer (see Figure 2, from the link above).
- Paste the code in the new blank window that opens
- Hit 'F5' on your keyboard to run

If it works as expected, i can show you how to tie the macro to a "Run" button on the sheet.

haha, thanks... I thought "ExcelWTF" would be an apt name

Hope that helps
Caleeco

 

[B Squared]

Caleeco, you are a frappin' genius!! This works perfectly...You're coding was clear enough for my small mind to make a few changes: I changed my mind on the "clear form with fewer legs", so I removed that line of code. I was also able to change the border to thin and continuous. So, I have just three more questions for you, and this project will be in the bag (Bet you're glad that you're the one who replied to my post, right?!?)

- They have added one final section to the form that is also leg dependent: "Additional Notes" Range A39:S48...I tried to integrate that range into your code and super-turbo-galactically-thunder-screwed the form. Thank goodness for test forms, undo, and copy/paste!!

- I would love to know how to attach the final code to a "run" button or similar, unless there is a way to make the row recalculation after the user tabs out, hits enter, or clicks out of C6?

- I have researched this last question until my head pops off, and I think it can't be done, but....I would like to be able to protect the form, allowing the user only access to the cells that they need. I really only need to be able to lock the top portion that I sent in my example above, as I have some drop down list text hidden, LastSaved functions, etc. But "autofit row height" does not seem to react well with protected sheets, and it is ultimately more important for the user to be able to enter unlimited notes. Figured I would get your expert thoughts on that!

If anyone asks me, you ARE the man!
B Squared

 

[Caleeco]

Hello B Squared,

Thanks for the positive feedback! My Excel-ego certainly appreciates it! Cool, glad it was easy enough to edit (As you dont need to clear the contents for 'less legs', I also deleted the line that counts how many legs there currently are - CurrLegs, as it's no longer needed). Onto your follow up queries:

1. I have assumed row A39 is where your first leg number (1) starts. So i have ammended the code to also alter range A39:S48.

2. Yes you can. So every time someone enters a new number into cell C6, the code will run. I have used the Worksheet Change Event to achieve this.

3. Yes also you can lock individual cells by modifying the cell properties. I can't do this for you and upload a file (against forum rules). But there is an easy guide to follow here:
https://support.office.com/en-gb/ar...orksheet-75481b72-db8a-4267-8c43-042a5f2cd93a

You should then be able to use Auto-Row height on non-protected cells in the notes section.

The code

Please delete the existing sub-routine, and paste this into the ThisWorkbook module.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim NoLegs As Integer
Dim i As Long
Dim ws As Worksheet


If Not Intersect(Target, Range("C6")) Is Nothing Then 'Set the range you want to monitor changing
    Set ws = Sheets("Sheet1") 'Change this to match your sheet name
    NoLegs = ws.Range("C6").Value 'User Entered
    
    'Only accepts legs 1-10
    If NoLegs < 1 Or NoLegs > 10 Then Exit Sub
    
    'Output new number of Legs
    For i = 1 To NoLegs
        ws.Range("A11").Offset(i).Value = i
        ws.Range("A38").Offset(i).Value = i
    Next i
    
    'Clear exisiting line style for both ranges
    ws.Range("A12:S21").Borders.LineStyle = xlNone
    ws.Range("A39:S49").Borders.LineStyle = xlNone
    
    'Set the borders for the new styles
    ws.Range("A11:S" & i + 10).BorderAround Weight:=xlThin, LineStyle:=xlContinuous
    ws.Range("A39:S" & 37 + i).BorderAround Weight:=xlThin, LineStyle:=xlContinuous
    
    'Hide unused rows
    ws.Range("A12:A21").EntireRow.Hidden = False
    ws.Range("A39:S48").EntireRow.Hidden = False
    If NoLegs < 10 Then
        ws.Range("A" & 22 - (10 - NoLegs) & ":A21").EntireRow.Hidden = True
        ws.Range("A" & 49 - (10 - NoLegs) & ":A48").EntireRow.Hidden = True
    End If
End If


End Sub

Let me know how you get on
Thanks

Caleeco

 

[B Squared]

Hi Caleeco,
Wow!! Thank you again, this works AMAZINGLY, save one issue that it created, probably from me not giving you enough info. I had in-cell formulas in A12:A21 to display the number of legs selected in C6. Basically if C6 is greater than X, then the cell populates the leg number automatically. I did this because I wrote a "Before Save" VBA that requires the user to populate a leg row with required info before saving. Example: if number of legs in C6 = "2", but the user failed to fill out Leg 2 "Origin", "Destination", etc. a message box would appear when they tried to save, reminding them what required information from the row is missing. Using the code you provided, the sheet is now reduced and lengthened to the correct number of legs PERFECTLY, but that in-cell formula is lost. So even if the number of legs requested is "1", when I hit the Save button it goes through all of my "Before Save" VBA Message boxes for all Legs (1-10.) Maybe I need to code in VBA to recalculate and populate A12:A21 based on C6, so my "Before Save" message box VBA will work correctly?

Hmmmm......I played around with protecting the sheet with the appropriate cells locked or unlocked. For some reason once protected, on the cells that are autofit cell height and with wrapped text, it seems to lock the cell after you enter info and exit that cell; you can't go back into that cell to change any information.

Thank you!
B Squared

 

[Caleeco]

Hi B Squared,

Glad to hear the sheet it somewhat working. I can't advise with the greatest of certainty wihtout seeing your formulas or your before save VBA code. However, if you want to retain exisiting formulas in A12:A21, just delete this block of code:

    'Output new number of Legs
    For i = 1 To NoLegs
        ws.Range("A11").Offset(i).Value = i
        ws.Range("A38").Offset(i).Value = i
    Next i

Are you sure you set the prppeties of those specific cells to Unlocked by Selecting > Right Click > Format Cells > Protection > Uncheck Locked?

Caleeco