+ Reply to Thread
Results 1 to 2 of 2

Fill cell with a value from List Validation applied

Hybrid View

  1. #1
    Registered User
    Join Date
    11-29-2012
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    4

    Fill cell with a value from List Validation applied

    Hello all,
    I'm working on a macro that checks if list validation rule is applied to a cell and if it is empty, fill its content from one of the values available in the list.
    I'm currently facing a problem that the setting of up of value is applied even if there is no list validation rule applied to it.
    My sample data in a worksheet

    Header
    List validation rule Applied but empty
    List validation rule Applied but empty
    List validation rule Applied but empty
    .
    .
    .
    Header
    List validation rule Applied but empty
    List validation rule Applied but empty
    List validation rule Applied but not empty
    .
    .
    .

    Header
    List validation rule Applied but empty
    List validation rule Applied but empty
    List validation rule Applied but empty
    .
    .
    .


    The problem is even though list validation rule is not applied to headers, the value is applied to them. I would like to know a way to stop this.

    Sub FillDefaults()
        'Open the selected workbooks and apply default values
        Dim currentSheet As Worksheet
        Dim cellRange As Range
        
        With Application.FileDialog(msoFileDialogFilePicker)
            .AllowMultiSelect = True
            .Show
            .Filters.Add "Excel 2003", "*.xls"
            .Filters.Add "Excel 2007", "*.xlsx"
            
            For counter = 1 To .SelectedItems.Count
                Workbooks.Open (.SelectedItems(counter))
                For Each currentSheet In ActiveWorkbook.Sheets
                    'For every sheet, start from the cell with first test condition
                    'If the cell is a test condition and a validation rule applied
                    'then make the default choice to its status
                    Dim cellCounter As Integer
                    Dim cellValue As Variant
                    Dim Rng As Range
                    
                    cellCounter = 2
                    cellValue = currentSheet.Range("A" & cellCounter).Value
                    
                    Do While cellValue <> ""
                        'check if corrsponding test conditions's status has any
                        'list validation applied and apply default values
                        On Error Resume Next
                        Set Rng = Intersect(currentSheet.Range("B" & cellCounter), Cells.SpecialCells(xlCellTypeSameValidation))
                        On Error GoTo 0
                        If Rng Is Nothing Then
                           If currentSheet.Range("B" & cellCounter).Value = "" Then
                              currentSheet.Range("B" & cellCounter).Value = "In Progress"
                           End If
                        End If
                                            
                        cellCounter = cellCounter + 1
                        cellValue = currentSheet.Range("A" & cellCounter).Value
                    Loop
                Next
                ActiveWorkbook.Save
                ActiveWorkbook.Close
            Next
        End With
    End Sub

  2. #2
    Registered User
    Join Date
    11-29-2012
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Fill cell with a value from List Validation applied

    Sorry for cross posting. I'm marking this as solved as I've opened it on the other community mrexcel.

    Don't crosspost!!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How to fill an activex combobox with a data validation list?
    By Sape in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-03-2013, 04:45 PM
  2. Fill list validation without range
    By Jivo in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-22-2009, 09:07 AM
  3. Fill listbox after autofilter has been applied
    By bdn435 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-22-2007, 02:29 AM
  4. auto-fill a data validation drop down list
    By carsto in forum Excel General
    Replies: 1
    Last Post: 04-11-2007, 11:56 AM
  5. [SOLVED] I've applied a Fill and now can't see the gridlines....
    By FizzyBunghole in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 01-19-2005, 12:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1