# VBA to change data validation selection when closing the file



## MMEmt (Dec 27, 2022)

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 (Dec 28, 2022)

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 (Dec 28, 2022)

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.xlsmBCDEFGHI1MME Trip Sheet Generator23Select the vehicle to generate the Trip Sheet for:Select the Month:Select the Year:Instructions:41Add/Remove names as needed on the Location-People page52Add/Remove vehicles as needed on Car List63Go to Trip Sheet Generator74Select Vehicle from Dropdown85Select the Month96Select the Year107Click the Macro Button118Review129Print number of copies needed1310Repeat as necessary 1411Save15161718192021222324Trip Sheet Generator

Here is the Location-People sheet: 
Trip Sheets Project Demo.xlsmAB1ABCDHermione Granger.2Elizabeth Bennet.3Matilda Wormwood.4Atticus Finch.5Katniss Everdeen.6Jane Eyre.7Gandalf8Eowyn from The Lord of the Rings910EFGHJay Gatsby11Holden Caulfield12Humbert Humbert13Leopold Bloom14Rabbit AngstromLocation-People

Car List Sheet:
Trip Sheets Project Demo.xlsmA12002 CHEVY BUS @  9TH AVE - POLSON   #122004 Silver DodgeTruck @ PWAC/NT #232010 DODGE CARAVAN @ MVGH    #342004 BLUE DODGE TRUCK @ RAC/TBN #452020 FORD BUS @ OVGH - POLSON #5 61997 Ford Bus @  71994 BUICK WAGON @ PWAC #782005 Chevy Uplander (Medical) @ OVGH  #892007 CHRYSLER @ PWAC - #9102009 Ford Focus @ PWAC  #10112011 HONDA PILOT @ POLSON/PWAC TRANSPORTATION #11121991 Buick Regal @ PWAC -  #12132021 Toyota Sienna @ ADMIN #13142018 Toyota RAV4 @ Admin #14152004 Dodge Caravan @ BSGH #15162006 Dodge Van (Medical) @ BSGH -  #16172006 Chrysler @ MVGH - RONAN #17182012 Chrysler 200 @ PWAC/LEAD's VEHICLE #18192015 Ford Bus  @ BSGH - #19202017 Toyota Sienna @ MVGH - #20212016 DODGE GRAND CARAVAN @ ROSR Ronan - #21222017 DODGE VAN W-SIDE RAMP @ ADMIN / NURSE/ MEDICAL - #22232016 Suburu Outback @ PWAC - #23Car List

Other_Data Sheet:
Trip Sheets Project Demo.xlsmABCDEFGHIJKLMNOPQRST1January2022ODOMETERTEST COMPANY2February2023AT BEGINNINGDRIVER'S TRIP RECORD3March20244April5MayODOMETER6JuneAT END7July8August9SeptemberDATE10October11NovemberINITIALS12December13DEPARTURE14TIME15ARRIVAL16TIMEOther_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 (Dec 28, 2022)

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 (Dec 28, 2022)

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


kevin9999 said:


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



To this:

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


----------



## MMEmt (Dec 28, 2022)

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 (Dec 28, 2022)

Glad you got it to work


----------

