VBA Data Validation, How to select 1st item on the list?

**D13L** · 03-01-2021, 07:00 AM

I created Data Validation in VBA.

AK = "10.89,10.8,14.34"

How can I choose the first item on the list? (or any in the list)
The sheet is leaving a blank.

Code:

Windows("New_Data.xlsx").Activate
Sheets("MAIN_DATA").Select
    .Range("AK" & SymNum).Delete
    With Sheets("MAIN_DATA").Range("AK" & SymNum).Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=AK
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
        .Value = Range(Replace(.Validation.Formula1, "=", "")).Cells(1, 1) 'Added this. I know it's wrong
    End With

My first post, please excuse newbie question.
Self taught VBA newbie.
Current skill: above "record macro".

**NeedForExcel** · 03-01-2021, 07:30 AM

Hi,

Sub FirstItem()

    Dim C
    
    C = Split(ActiveCell.Validation.Formula1, ",")(0)
    
    ActiveCell.Value = C
    
End Sub

This will give you the 1st item of the Data Validation. You can change the (0) to get the desired item.

Also, if the numbers are of Double data type, make sure you convert it if needed.

Also, change the Activecell to the desired reference

**D13L** · 03-01-2021, 07:52 AM

Dave Thank you,
That solved it. I just kinda sucks that I couldn't find a way to do it in one line inside the // With Sheets.

VBA Data Validation, How to select 1st item on the list?

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

VBA Data Validation, How to select 1st item on the list?

Re: VBA Data Validation, How to select 1st item on the list?

Re: VBA Data Validation, How to select 1st item on the list?

Thread Information

Users Browsing this Thread

Similar Threads

Select item of a dropdown list (data validation) and refresh one pivot table

Select next/previous item from data validation list using Command Button

[SOLVED] Select item of a dropdown list (data validation) and refreshes values of all pivot tables

macro to select each item in data validation list

Macro to select an item from a data validation list

easy way to select an item from a very long data validation list

Macro to select an item in a Validation List

Bookmarks

Bookmarks

Posting Permissions