VBA to change data validation selection when closing the file

[MMEmt]

Mostly newb at coding, but can follow it ok.
I've looked for, and probably overlooked, the answer to this. When the workbook closes, one worksheet needs to have the values of some data validation dropdowns set back to either blank, or to the first selection in the list. Having tried multiple versions of different code that others posted, success has still eluded me.

It should fit in the code below. Right now I have it in a stand alone Sub TestMe for, ironically, testing. Eventually it will run when the workbook is closed.

Sub TestMe()

'need to reset "Drop Down 7", "Drop Down 8", and "Drop Down 9" on worksheet named "Trip Sheet Generator" to either blank or default values from data validation on Other_Data and Car List worksheets
 'enter code here
   
'This section deletes Output to Print worksheet
For Each ws In ThisWorkbook.Worksheets
    If ws.Name = "Output to Print" Then
        Application.DisplayAlerts = False
        Sheets("Output to Print").Delete
        Application.DisplayAlerts = True
    End If
Next
'This forces a save on the workbook

ThisWorkbook.Save
MsgBox "This Workbook is Saved"
   
   
End Sub

Can anybody point me in the right direction? It is much appreciated!

 

[kevin9999]

There's a number of ways you could do this. If, for example, your "Drop Down 7", "Drop Down 8", and "Drop Down 9" are references to specific cells (let's say cells B2, D2 and F2) on the "Trip Sheet Generator" sheet, and you wanted to leave them blank on workbook close, you could use this:

Sheets("Trip Sheet Generator").Range("B2,D2,F2") = ""

Alternatively, if you wanted to leave them with the default value on closing, and the data validation lists happened to be named ranges (let's say "NamedRange" for the sake of this demo), then you could use something like this:

Sheets("Trip Sheet Generator").Range("B2") = _
Sheets("Other_Data").Range("NamedRange").Cells(1)

We really need more information to assist you further, but hopefully, this has pointed you in the right direction. For future reference, look at using the XL2BB add in to provide us with a sample of your sheet to work with.

 

[MMEmt]

Thank you for your response. I got the XL2BB set up (and learned about Trust Center and Add-in Options along the way). I hope that this attempt turns out well and gives you everything you need.

Oh, I have not used named as ranges as I haven't learned to use them, but if needed to accomplish this task I certainly will.

Here is the Trip Sheet Generator Sheet. I do not believe that I have the dropdowns associated to the cells themselves. I don't know how to do that, and when I run the code you provided (Sheets("Trip Sheet Generator").Range("B2,D2,F2") = ""), the dropdowns remain with the last selection still shows.

Trip Sheets Project Demo.xlsm
	B	C	D	E	F	G	H	I
1		MME Trip Sheet Generator
2
3		Select the vehicle to generate the Trip Sheet for:		Select the Month:		Select the Year:	Instructions:
4							1	Add/Remove names as needed on the Location-People page
5							2	Add/Remove vehicles as needed on Car List
6							3	Go to Trip Sheet Generator
7							4	Select Vehicle from Dropdown
8							5	Select the Month
9							6	Select the Year
10							7	Click the Macro Button
11							8	Review
12							9	Print number of copies needed
13							10	Repeat as necessary
14							11	Save
15
16
17
18
19
20
21
22
23
24
Trip Sheet Generator

Here is the Location-People sheet:

Trip Sheets Project Demo.xlsm
	A	B
1	ABCD	Hermione Granger.
2		Elizabeth Bennet.
3		Matilda Wormwood.
4		Atticus Finch.
5		Katniss Everdeen.
6		Jane Eyre.
7		Gandalf
8		Eowyn from The Lord of the Rings
9
10	EFGH	Jay Gatsby
11		Holden Caulfield
12		Humbert Humbert
13		Leopold Bloom
14		Rabbit Angstrom
Location-People

Car List Sheet:

Trip Sheets Project Demo.xlsm
	A
1	2002 CHEVY BUS @ 9TH AVE - POLSON #1
2	2004 Silver DodgeTruck @ PWAC/NT #2
3	2010 DODGE CARAVAN @ MVGH #3
4	2004 BLUE DODGE TRUCK @ RAC/TBN #4
5	2020 FORD BUS @ OVGH - POLSON #5
6	1997 Ford Bus @
7	1994 BUICK WAGON @ PWAC #7
8	2005 Chevy Uplander (Medical) @ OVGH #8
9	2007 CHRYSLER @ PWAC - #9
10	2009 Ford Focus @ PWAC #10
11	2011 HONDA PILOT @ POLSON/PWAC TRANSPORTATION #11
12	1991 Buick Regal @ PWAC - #12
13	2021 Toyota Sienna @ ADMIN #13
14	2018 Toyota RAV4 @ Admin #14
15	2004 Dodge Caravan @ BSGH #15
16	2006 Dodge Van (Medical) @ BSGH - #16
17	2006 Chrysler @ MVGH - RONAN #17
18	2012 Chrysler 200 @ PWAC/LEAD's VEHICLE #18
19	2015 Ford Bus @ BSGH - #19
20	2017 Toyota Sienna @ MVGH - #20
21	2016 DODGE GRAND CARAVAN @ ROSR Ronan - #21
22	2017 DODGE VAN W-SIDE RAMP @ ADMIN / NURSE/ MEDICAL - #22
23	2016 Suburu Outback @ PWAC - #23
Car List

Other_Data Sheet:

Trip Sheets Project Demo.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	January		2022					ODOMETER												TEST COMPANY
2	February		2023					AT BEGINNING												DRIVER'S TRIP RECORD
3	March		2024
4	April
5	May							ODOMETER
6	June							AT END
7	July
8	August
9	September							DATE
10	October
11	November							INITIALS
12	December
13								DEPARTURE
14								TIME
15								ARRIVAL
16								TIME
Other_Data

This is the macro that is ran when clicking on the Generate Output to Print File button:

Sub OutputToFile()
Dim ws As Worksheet
Dim dd As DropDown
Dim DDVal As String
Dim iRange, iCells As Range
Dim i As Long


'delete and make new OutputToPrint sheet
For Each ws In ThisWorkbook.Worksheets
    If ws.Name = "Output to Print" Then
        Application.DisplayAlerts = False
        Sheets("Output to Print").Delete
        Application.DisplayAlerts = True
    End If
Next

Sheets.Add(After:=Sheets("Trip Sheet Generator")).Name = "Output to Print"
Worksheets("Output to Print").Activate
Windows(1).DisplayGridlines = False


'Copy corporate info

 Sheets("Other_Data").Select
    Range("T1:T2").Select
    Selection.Copy
    Sheets("Output to Print").Select
    Range("B1").Select
    ActiveSheet.Paste

'Copy Vehicle info and paste into Output to Print

Set dd = Sheets("Trip Sheet Generator").DropDowns("Drop Down 7")
DDVal = dd.List(dd.ListIndex)
ThisWorkbook.Sheets("Output to Print").Range("C78").Value = DDVal

'Copy Month and paste into Output to Print

Set dd = Sheets("Trip Sheet Generator").DropDowns("Drop Down 8")
DDVal = dd.List(dd.ListIndex)
ThisWorkbook.Sheets("Output to Print").Range("H2").Value = DDVal

'Copy year and paste into Output to Print
Set dd = Sheets("Trip Sheet Generator").DropDowns("Drop Down 9")
DDVal = dd.List(dd.ListIndex)
ThisWorkbook.Sheets("Output to Print").Range("I2").Value = DDVal


'Copy and paste Odometer, etc info
   Sheets("Other_Data").Select
    Range("H1:R16").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Output to Print").Select
    Range("C62").Select
    ActiveSheet.Paste
    
'Copy and paste Locations and Names

  Sheets("Location-People").Select
    Range("A1:B58").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Output to Print").Select
    Range("B4").Select
    ActiveSheet.Paste
    
'Format Sheets

    'Corporate and data
    Sheets("Output to Print").Select
    With Range("B1:B2").Font
        .Name = "Arial"
        .Size = 10
        .FontStyle = "Bold"
    End With
    
        With Range("H2:I2").Font
        .Name = "Arial"
        .Size = 10
        .FontStyle = "Bold"
    End With

    'Names
    
    'Sheets("Output to Print").Select
    With Range("C4:C61").Font
        .Name = "Calibri Light"
        .Size = 9
        .FontStyle = "Bold"
    End With

    'Odometer formatting
   ' Sheets("Output to Print").Select
    With Range("C62:C77").Font
        .Name = "Albertus Extra Bold"
        .Size = 8
    End With

    'Autofit columns
    'Sheets("Output to Print").Select
    Worksheets("Output to Print").Columns("C:C").AutoFit
    
    'Merge cells for car and format
    'Sheets("Output to Print").Select
    Range("C78:M78").Select
    
    With Selection
        .Interior.ColorIndex = 6
        .HorizontalAlignment = xlCenter
        .MergeCells = True
        .Name = "Arial"
        With .Font
            .Size = 14
            .FontStyle = "Bold"
            .Underline = xlUnderlineStyleSingle
        End With
        
    End With
    
    'Format cell borders
    'Whole grid goes from B4 to M61
    'Sheets("Output to Print").Select
    Set iRange = Range("C4:M60")
    For Each iCells In iRange
        iCells.BorderAround _
            LineStyle:=xlContinuous, _
            Weight:=xlThin
        Next iCells
    'Now do the same for D3 through M3
    'Sheets("Output to Print").Select
    Set iRange = Range("D3:M3")
    For Each iCells In iRange
        iCells.BorderAround _
            LineStyle:=xlContinuous, _
            Weight:=xlThin
        Next iCells
        
                            
' Find blank spaces and do a thick underline
   ' Sheets("Output to Print").Select
    Set iRange = Range("B2:C60")
    For Each iCells In iRange
    If IsEmpty(iCells) Then
        'if the next row down is not empty then
        If Not IsEmpty(iCells.Offset(1, 0)) Then
        For i = 0 To 10
          iCells.Offset(0, i).Borders(xlEdgeBottom).Weight = xlThick
        Next i
        End If
    End If
        Next iCells

End Sub

And again, here is the macro I am trying to work on to reset the dropdowns back to empty

Sub TestMe()

Dim ws As Worksheet
 
  
    Sheets("Trip Sheet Generator").Range("B2,D2,F2") = ""
    
    For Each ws In ThisWorkbook.Worksheets
    If ws.Name = "Output to Print" Then
        Application.DisplayAlerts = False
        Sheets("Output to Print").Delete
        Application.DisplayAlerts = True
    End If
Next
    'ThisWorkbook.Save
    'MsgBox "This Workbook is Saved"
    
    

    
End Sub

 

[MMEmt]

As I can't edit my own posts here are 3 things:
1) The fields to be cleared are "C5,E5,G5", not the "B2",etc, ones I had in the code.
2) When doing the XL2BB it doesn't seem to capture the buttons and dropdowns, so here is the screenshot of that:

 

[kevin9999]

Let's take this one step at a time. Try changing this line:

Sheets("Trip Sheet Generator").Range("B2,D2,F2") = ""

To this:

Sheets("Trip Sheet Generator").Range("C5,E5,G5") = ""

 

[MMEmt]

Thanks again for assisting. I made that change and it still doesn't clear the dropdowns. (A few minutes pass....)

Ah, I just looked carefully and realized that the dropdown are "floating" over those cells. Somehow I made the data validation/dropdown outside of the cells, rather than in them. It works as it should with your code.

Thank you, thank you!

 

[kevin9999]

Glad you got it to work