VBA to automatically loop through validation list with any change

mgerving

New Member
Joined
Mar 18, 2010
Messages
33
I have a validation list that when a specific date is selected, it runs a "PasteData" macro.
How do I insert code to automatically loop through all the dates in the list, any time there is a change in the Sheet?
The PasteData sub runs perfectly, just trying to make this more automatic.

Sub Worksheet_Change(ByVal Target As Range)
Set Target = Range("B3")
If Target.Value = "Nov 20" Then
Call PasteData
End If
Set Target = Range("B3")
If Target.Value = "June 21" Then
Call PasteData
End If
Set Target = Range("B3")
If Target.Value = "Nov 21" Then
Call PasteData
End If
Set Target = Range("B3")
If Target.Value = "June 22" Then
Call PasteData
End If
Set Target = Range("B3")
If Target.Value = "Nov 22" Then
Call PasteData
End If
Set Target = Range("B3")
If Target.Value = "June 23" Then
Call PasteData
End If
Set Target = Range("B3")
If Target.Value = "Nov 23" Then
Call PasteData
End If
End Sub
 
Do you still get the "No validation" msgbox?
 
Upvote 0

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
I haven't done anything after I sent xl2bb shots and a copy of the code that I have in a mockup sheet that I created.
On the mockup workbook that I created, I do still get the "No validation" error and the values don't copy paste with that code.
 
Upvote 0
Would you be willing to share the workboook?
If so, upload it to a share site such as OneDrive, DropBox, Google Drive, mark for sharing & then post link you get to the thread.
 
Upvote 0
Thanks for that, unfortunately it's asking me to sign in. You need to mark the file for sharing & then post the link you are given.
 
Upvote 0
Ok, thanks for that, how about
VBA Code:
Sub mgerving()
   Dim Cl As Range, ListRng As Range
   Dim Ary As Variant
   Dim i As Long

   Set Cl = Range("E3")
   On Error Resume Next
   Set ListRng = Range(Replace(Cl.Validation.Formula1, "=", ""))
   On Error GoTo 0
   
   If ListRng Is Nothing Then
      MsgBox "No validation"
      Exit Sub
   End If
   
   If ListRng.Rows.Count = 1 Then
      For i = 1 To ListRng.Columns.Count
          Cl.Value = Range(Replace(Cl.Validation.Formula1, "=", "")).Cells(1, i)
          Application.Calculate
          Call PasteData
      Next i
   Else
      For i = 1 To ListRng.Rows.Count
          Cl.Value = Range(Replace(Cl.Validation.Formula1, "=", "")).Cells(i, i)
          Application.Calculate
          Call PasteData
      Next i
   End If
End Sub
This needs to go in a standard module, not a sheet module.
You also need to delete the change event code you have already got.
 
Upvote 0
Worked great. Moved the PasteData to the same new Module2, and changed the Blend Sheet command button to call the sub mgerving so that before it goes to the Blend sheet, it runs the sub.

Private Sub Blend_Click()
Call mgerving
Sheets("Blend").Select
Sheets("Blend").Range("B2").Select
End Sub

Thanks for the help Fluff!!
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0
Solution

Forum statistics

Threads
1,212,927
Messages
6,110,700
Members
448,293
Latest member
jin kazuya

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
Back
Top