Automatically reset drop-down list at the end of a loop through to select first item

Bering

Board Regular
Joined
Aug 22, 2018
Messages
134
Office Version
  1. 2016
Platform
  1. Windows
Hello,

I use the below bit of code to loop through all items in a cell which contains a drop-down list (data validation) and to pdf the ActiveSheet.

It works great, apart for a minor problem: since the macro ends by selecting the last item in the drop-down list, every time, I have to manually "reset" it to select the first item.

I was wondering if there is way to have the macro always select the first item in the list, irrespective of what item is currently select, and also to automatically reset the list at the end of the loop.

Thank you.

VBA Code:
'''' Location of DataValidation cell
Set r = Worksheets("Call Letter").Range("C2")
'''' Get DataValidation values
Set inputRange = Evaluate(r.Validation.Formula1)

'''' Loop through DataValidation list

For Each c In inputRange
If c <> "" Then

    r.Value = c.Value
    fName = c.Value
   
    Worksheets("Call Letter").ExportAsFixedFormat Type:=xlTypePDF, Filename:=FolderName & ActiveSheet.Range("AttachFileName"), _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        
  
End If
Next c
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,619
Office Version
  1. 2007
Platform
  1. Windows
How about

VBA Code:
  Dim first As Variant
  '''' Location of DataValidation cell
  Set r = Worksheets("Call Letter").Range("C2")
  '''' Get DataValidation values
  Set inputRange = Evaluate(r.Validation.Formula1)
  
  '''' Loop through DataValidation list
  For Each c In inputRange
    If first = "" Then first = c.Value
    If c <> "" Then
      r.Value = c.Value
      fName = c.Value
      Worksheets("Call Letter").ExportAsFixedFormat Type:=xlTypePDF, Filename:=FolderName & _
        ActiveSheet.Range("AttachFileName"), Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    End If
  Next c
  r = first
 
Solution

Bering

Board Regular
Joined
Aug 22, 2018
Messages
134
Office Version
  1. 2016
Platform
  1. Windows
Yesss, that's perfect.

Thank you so much
(y)(y)(y)(y)(y)(y)(y)

How about

VBA Code:
  Dim first As Variant
  '''' Location of DataValidation cell
  Set r = Worksheets("Call Letter").Range("C2")
  '''' Get DataValidation values
  Set inputRange = Evaluate(r.Validation.Formula1)
 
  '''' Loop through DataValidation list
  For Each c In inputRange
    If first = "" Then first = c.Value
    If c <> "" Then
      r.Value = c.Value
      fName = c.Value
      Worksheets("Call Letter").ExportAsFixedFormat Type:=xlTypePDF, Filename:=FolderName & _
        ActiveSheet.Range("AttachFileName"), Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    End If
  Next c
  r = first
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,619
Office Version
  1. 2007
Platform
  1. Windows
Im glad to help you. Thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,499
Messages
5,636,682
Members
416,935
Latest member
Atulcp

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top