Solved Excel VBA Add Data Validation w Named Range Error

Microsoft Excel 2010 - complete product...
October 8, 2014 at 07:37:18
Specs: Windows 7 Service Pack 1
Hi,

I am creating an excel spreadsheet that I am adding checkboxes, formatting and a data validation list using vba. I am having issues with the Data Validation area when it gets to the area bold and italic below, I get a Run-time error '1004'. I have been researching and trying different things and not finding anything that will help.

Option Explicit

Sub Addcheckboxes_Format()
Dim cell, LRow As Single
Dim chkbx As CheckBox
Dim MyLeft, MyTop, MyHeight, MyWidth As Double
Dim c As Range

Application.ScreenUpdating = False
LRow = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row

'Add Checkboxes
For cell = 7 To LRow
    If Cells(cell, "B").Value <> "" Then
        MyLeft = Cells(cell, "D").Left + 3.5
        MyTop = Cells(cell, "D").Top
        MyHeight = Cells(cell, "D").Height
        MyWidth = Cells(cell, "D").Width
        ActiveSheet.CheckBoxes.Add(MyLeft, MyTop, MyWidth, MyHeight).Select
        With Selection
            .Caption = ""
            .Value = xlOff
            .Display3DShading = False
        End With
    End If
    
        If Cells(cell, "B").Value <> "" Then
        MyLeft = Cells(cell, "E").Left + 3.5
        MyTop = Cells(cell, "E").Top
        MyHeight = Cells(cell, "E").Height
        MyWidth = Cells(cell, "E").Width
        ActiveSheet.CheckBoxes.Add(MyLeft, MyTop, MyWidth, MyHeight).Select
        With Selection
            .Caption = ""
            .Value = xlOff
            .Display3DShading = False
        End With
    End If
        
        If Cells(cell, "B").Value <> "" Then
        MyLeft = Cells(cell, "F").Left + 3.5
        MyTop = Cells(cell, "F").Top
        MyHeight = Cells(cell, "F").Height
        MyWidth = Cells(cell, "F").Width
        ActiveSheet.CheckBoxes.Add(MyLeft, MyTop, MyWidth, MyHeight).Select
        With Selection
            .Caption = ""
            .Value = xlOff
            .Display3DShading = False
        End With
    End If
        
        If Cells(cell, "B").Value <> "" Then
        MyLeft = Cells(cell, "G").Left + 3.5
        MyTop = Cells(cell, "G").Top
        MyHeight = Cells(cell, "G").Height
        MyWidth = Cells(cell, "G").Width
        ActiveSheet.CheckBoxes.Add(MyLeft, MyTop, MyWidth, MyHeight).Select
        With Selection
            .Caption = ""
            .Value = xlOff
            .Display3DShading = False
            .LinkedCell = .TopLeftCell.Offset(0, 5).Address
        End With
    End If
        
        If Cells(cell, "B").Value <> "" Then
        MyLeft = Cells(cell, "I").Left + 12
        MyTop = Cells(cell, "I").Top
        MyHeight = Cells(cell, "I").Height
        MyWidth = Cells(cell, "I").Width
        ActiveSheet.CheckBoxes.Add(MyLeft, MyTop, MyWidth, MyHeight).Select
        With Selection
            .Caption = ""
            .Value = xlOff
            .Display3DShading = False
        End With
    End If

        If Cells(cell, "B").Value <> "" Then
        MyLeft = Cells(cell, "J").Left + 12
        MyTop = Cells(cell, "J").Top
        MyHeight = Cells(cell, "J").Height
        MyWidth = Cells(cell, "J").Width
        ActiveSheet.CheckBoxes.Add(MyLeft, MyTop, MyWidth, MyHeight).Select
        With Selection
            .Caption = ""
            .Value = xlOff
            .Display3DShading = False
        End With
    End If

Next cell

Application.ScreenUpdating = True

'Add Borders
    Dim LR As Long
    LR = Range("B" & Rows.Count).End(xlUp).Row
    With Range("B7:K" & LR)
        .Borders.Weight = xlThin
        .BorderAround Weight:=xlThin
    End With
       
'Add Data Validation
    LR = Range("B" & Rows.Count).End(xlUp).Row
    
    With Range("H7:H" & LR)
        With .Validation
            .Delete
<b>  <i>          .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:="=Age"</i></b>   
         End With
    End With
    Range("B4").Select

End Sub

Also, if there is a faster way I could add the checkboxes that would be very helpful as well.

Thank you,
Sandi


See More: Excel VBA Add Data Validation w Named Range Error

Report •


#1
October 8, 2014 at 12:01:38
✔ Best Answer
First, a posting tip...

Thank you for using the pre tags to post your VBA code, but you should be aware that no other formatting can be done within the pre tags so your Bold and Italics didn't work.

As far as your questions...

I don't do much with CheckBoxes, so I don't have any suggestions as to a faster way to create them. A Google search for something related to "VBA code to add CheckBoxes" will probably turn up some suggestions.

Your error is happening because the last CheckBox that you Add is still Selected. Excel won't let VBA add the data validation while that CheckBox is selected.

While I usually rant about the fact that it is rare that objects within VBA have to be Selected to be operated on, I don't know enough about creating CheckBoxes with VBA to offer a way to create them without .Select. However, since you end your code with...

Range("B4").Select

...why not move that instruction up above your "Add Data validation" section so the CheckBox is no longer the Selected object? Once that's done, your code should work fine.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#2
October 8, 2014 at 12:10:03
Oh my that is exactly what I needed and now it works perfectly!! Also thank you for the posting tip, I thought that it looked a little odd!!! I will research the checkboxes a little more but I think it is do'able for now.

Thanks again,
Sandi


Report •
Related Solutions


Ask Question