NH VBA code to auto refresh the default option in a drop down

mdiangelo

New Member
Joined
Mar 25, 2013
Messages
4
Good Afternoon Excel Guru’s,

So my latest issue is this: I would like a data validation list to auto refresh the first option in a drop down using a VBA code.
So please allow me to back fill you in on what’s happening prior to the situation above. This program has many end users that extract four reports from a website and export into CSV files. My program then imports the data into a table that’s used by 17 different pivot tables, from there the tab “MainTab” complies all the new information a displays it vividly for the end user to see.
<o:p></o:p>
The Layout:
Column A 342:370 contains Training Topics that are fixed and used by columns C:ALN
Column B: is the Date when the training was conducted.This is a Data validation list of three values. The formula for mode of all the dates within that row,“N/A”, “ “
Columns C:ALN Indexmatch the topic in A column with the person’s name in row 342 from a pivot table on a different tab "CNAF" to give the date. When no date is available the formula then compares the date they checked into the work center and the date the training was conducted to display either “NotChkdIn” or “Missed_it”. If "N/A" was selected in the B column then the entire row will display "N/A".
<o:p></o:p>
My issue is this:
Column B always saves the last date that was entered. I would like it to auto-refresh the first option which is a formula for mode. The Data Validation drop downs use tiles ALU343:370 for mode ALV343:370 for “N/A”, and ALW343:370 for the blank space (this allows the user to input any date they would like) So an example is row 343 will use the validation options ALU343-ALV343-ALW343.

P.S. Obivously Im pretty new at the forum, so please bear with me. And why would my ability to post an attchment be turned off?
Excel 2007 Windows XP

 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

mdiangelo

New Member
Joined
Mar 25, 2013
Messages
4
So I've mad some head way with the above problem with this:

Set c = Range("B79")
c.Value = Range(Mid(c.Validation.Formula1, 2)).Cells(1, 1).Value


but i have to copy and paste for each of the drops down I have. I did try this:

Set c = Range("B79:B106")
c.Value = Range(Mid(c.Validation.Formula1, 2)).Cells(1, 1).Value

but it dosen't seem to allow the time needed for the formula for the first option to calculate.

and lastly I'd like to add an IF statement. I've come up with this but it fails to work correctly:

Sub Default_First_Option()


'if cell value is a date then refresh to the data validation's first option.
'if cell value is N/A then leave it alone go to the next cell. N/A is the second option in the drop down
'The third option is a blank to the end user can enter any date they would like.

On Error GoTo 1
1: MsgBox "FAIL"

Sheets("Sheet1").Select
For Each cell In Range("B79:B106")
Select Case cell.Value
Case "*-???-??" 'this would be any date value. ex 29AUG13
cell.Range(Mid(c.Validation.Formula1, 2)).Cells(1, 1).Value
Case "N/A"
c.Value = Range(Mid(c.Validation.Formula1, 2)).Cells(1, 2).Value
End Select
Next cell


End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,122,694
Messages
5,597,571
Members
414,157
Latest member
MrBJBones

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