VBA ValidateList

[mar050703]

Hello,

I am trying to write some code that will allow me from a userform to place a validate list option in cell, but default to the first item in that list. I have the following code which seems to work to enable the cell to become a validatelist.

Private Sub CommandButton1_Click() 
    Dim rCl As Range 
     
    With Sheets("Sheet1") 
        Set rCl = .Cells(.Rows.Count, 1).End(xlUp).Offset(1) 
    End With 
    rCl.Value = txtamt 
     
    With rCl.Offset(0, 3).Validation 
        .Add xlValidateList, xlValidAlertStop, xlBetween, "=Name" 
        .InCellDropdown = True

    End With

and I am using the following code to automatically select the 1st item, but it fails with an invalid qualifier error.

 rCl.Value = Replace(ActiveCell.Validation.Formula1, "=", "").Cells(1, 1).Value

Thank you for any assistance

Mar050703

 

[SkipVought]

hi,

Not really sure what you are TRYING to do.

Your code inserts a new value in txtamt in the NEXT cell in your list, in column A. I'm guessing that your list is named Name, but unless you dynamically name your range, the new value is never included in the Name range.

Regarding the Data Validation list, each time you run this code, it puts a new DV incell in the same row as the last insert into the list. Do you want a VALUE in that cell?

It's not at all clear what you're trying to do!

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[mar050703]

Thankyou or your response

I am sorry this is the relevant code for what I am trying to do, the fist bit (which I did not post, find the first blank cell, and from there will enter the relevant data from a user form.

My problem is that the data on the spreadsheet, is updated later, and in this case the validate list, should default to the first item, but allow me later to change the item say from 'Live' to 'Cancelled' or 'Completed'

The full code is:

Private Sub CmdOk_Click()
Dim OrderDate As Date 'Order Date
Dim CustName As String 'Customer Name
Dim email As String 'Email
Dim rCl As Range
OrderDate = TxtOrderDate.Text
TxtOrderDate = Format(OrderDate, "dd-mmm-yy")
CustName = TxtCustName
email = TxtEmail

'Sheets("Sheet1").Select
'Range("A21").Select

'Do
   ' If IsEmpty(ActiveCell) = False Then
   ' ActiveCell.Offset(1, 0).Select
'End If
'Loop Until IsEmpty(ActiveCell) = True
With Sheets("Sheet1")
    Set rCl = .Cells(.Rows.Count, 1).End(xlUp).Offset(1)
End With

rCl.Value = TxtOrderDate.Text
rCl.Offset(0, 1) = TxtCustName
rCl.Offset(0, 2) = TxtEmail
With rCl.Offset(0, 3).Validation
    .Add xlValidateList, xlValidAlertStop, xlBetween, "=status"
    .InCellDropdown = True
cell.Value = Split(cell.Validation.Formula1, ",")(0)
End With

I am sorry I am new to VBA and only self taught.

Thanks for your patience.

mar050703

 

[SkipVought]

What does, "the validate list, should default to the first item" mean? Please explain in detail what you intend to happen. You can only do with VBA what you can do on the sheet, and you cannot supply a default value on the sheet. A DV In-Cell LIST, has no default value, unless your actually insert the first value into the cell as if it had been selected from the list.



Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[mar050703]

Hello

What I am trying to do is with the use of a userform, when I enter Ok, scroll through with the answers of the userform, but in a particular cell, I need t place a dropdown list into the cell, but cause it to default to the first item in the list (for me to change manually at a later date).

In a test sheet, I can get it to work, but with the settings of the actualy sheet, it appears not to work. The code I am using for the test is:

 Range("D21").Select
Selection.Value = txtdate.Value
ActiveCell.Offset(0, 1).Select

With Selection.Validation
 .Add xlValidateList, xlValidAlertStop, xlBetween, "=Name"
 .InCellDropdown = True
 ActiveCell.Value = Range(Replace(ActiveCell.Validation.Formula1, "=", "")).Cells(1, 1).Value
 

End With

Thanks

 

[zelgar]

You might try the following code
ActiveCell.Value = "=Index(Name,1,1)

 

[zelgar]

if you don't want to see the formula, but just the number in the cell, then

ActiveCell.Value = "=INDEX(Name,1,1)"
num = ActiveCell.Value
ActiveCell.Value = num

The problem doing what you want in VB is that the array in the data validation is a named entry in Excel. Therefore, you'll either have to bring in the array in VB & determine the first item or go back & forth using Excel functions (i.e., INDEX) in order to get the first entry.

 

[mar050703]

Thanks, but whilst this will give me the first entry form the validate list, I need to cause the cell to have the validate list entered into it. So that when I come to that cell later, I have a drop down of choices.

 Private Sub CmdOk_Click()
Dim OrderDate As Date 'Order Date
Dim CustName As String 'Customer Name
Dim email As String 'Email
'Dim rCl As Range
OrderDate = TxtOrderDate.Text
TxtOrderDate = Format(OrderDate, "dd-mmm-yy")
CustName = TxtCustName
email = TxtEmail

Sheets("Sheet1").Select
Range("A21").Select

Do
    If IsEmpty(ActiveCell) = False Then
    ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
'With Sheets("Sheet1")
    'Set rCl = .Cells(.Rows.Count, 1).End(xlUp).Offset(1)
'End With

ActiveCell.Value = TxtOrderDate.Text
ActiveCell.Offset(0, 1) = TxtCustName
ActiveCell.Offset(0, 2) = TxtEmail
ActiveCell.Offset(0, 3).Select
ActiveCell.Value = "=Index(Status,1,1)"

How do I use xl validateList (as listed in the previous post, but default to the fist.

Would it help if I am able to post the workbook?

Thanks

 

[SkipVought]

Then

ActiveCell = Range("Name")(0)

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[mar050703]

Thank you for the response.

I get this to work in my test sheet, but not on my live sheet, I keep getting a run time error 1004.

The debug is highlighting

.Add xlValidateList, xlValidAlertStop, xlBetween, "=Status"

What is this, and how can I over come this?

Within the live sheet I do have a userform with varying text boxes, frames and comboboxes, and basically the code will put the details from the form into the next available row

Thanks.

 

[SkipVought]

Please post your code, not just a snippet.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[mar050703]

The entire code for presing 'ok' is:

 Private Sub CmdOk_Click()
Dim OrderDate As Date 'Order Date
Dim ShortDate As Date 'Short Date of Order Date
Dim CustName As String 'Customer Name
Dim email As String 'Email
'Dim rCl As Range
OrderDate = TxtOrderDate.Text
ShortDate = TxtshortDate.Text
TxtOrderDate = Format(OrderDate, "dd-mmm-yy")
TxtshortDate = Format(ShortDate, "mmm-yy")


'CustName = TxtCustName
'email = TxtEmail
'TO DO
    'Need to Format the £ and Date of AccFee etc
    
     'Need to arrange If's into 1 massive group of If's
'If ChkAccFee.Value = True And TxtAccInv.Value = False Then
'MsgBox "You must enter an Acceptance Fee Invoice Number", vbOKOnly
'GoTo Finish
'Else
'GoTo Continue
'End If

'If Inipay.Value = True And TxtIPInv.Value = False Then
'MsgBox "You must enter an Initial Payment Invoice Number", vbOKOnly
'GoTo Finish
'Else
'GoTo Continue
'End If

'If ChkCom.Value = ture And TxtComAmt = False Then
'MsgBox "You must enter a Commission Value", vbOKOnly
'GoTo Finish
'Else
'GoTo Continue
'End If

'If TMCYes = False And TMCNo = False Then
'MsgBox "You must select a TMC Type", vbOKOnly
'GoTo Finish
'Else
'GoTo Continue
'End If

'If OptNew.Value = False And OptPart.Value - False Then
'MsgBox "You select if the car is New or Part Ex", vbOKOnly
'GoTo Finish
'Else
'GoTo Continue
'End If


Continue:
Sheets("Sheet1").Select
Range("A21").Select

Do
    If IsEmpty(ActiveCell) = False Then
    ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
'With Sheets("Sheet1")
    'Set rCl = .Cells(.Rows.Count, 1).End(xlUp).Offset(1)
'End With

ActiveCell.Value = TxtOrderDate.Text
ActiveCell.Offset(0, 1) = TxtshortDate 'force short date
'force URN - ?
ActiveCell.Offset(0, 2) = TxtCustName
ActiveCell.Offset(0, 3) = TxtEmail
ActiveCell.Offset(0, 4) = TxtContNo
If TMCYes.Value = True Then
    ActiveCell.Offset(0, 5) = "Yes"
    Else
    ActiveCell.Offset(0, 5) = "No"
End If
ActiveCell.Offset(0, 6).Value = CombStkType.Value

If ChkDeposit.Value = True Then
    ActiveCell.Offset(0, 7) = TxtDepAmt
    ActiveCell.Offset(0, 8) = TxtDepDate
    ActiveCell.Offset(0, 9) = TxtDepInv
Else
    ActiveCell.Offset(0, 7) = 0
    ActiveCell.Offset(0, 8) = ""
    ActiveCell.Offset(0, 9) = ""
    
End If

If ChkAccFee.Value = True Then
    ActiveCell.Offset(0, 10) = TxtAccAmt
    ActiveCell.Offset(0, 11) = TxtAccDate
    ActiveCell.Offset(0, 12) = TxtAccInv
Else
    ActiveCell.Offset(0, 10) = 0
    ActiveCell.Offset(0, 11) = ""
    ActiveCell.Offset(0, 12) = ""
End If

If ChkIniPay.Value = True Then
     ActiveCell.Offset(0, 13) = TxtIPAmt
    ActiveCell.Offset(0, 14) = TxtIPDate
    ActiveCell.Offset(0, 15) = TxtIPInv
Else
    ActiveCell.Offset(0, 13) = 0
    ActiveCell.Offset(0, 14) = ""
    ActiveCell.Offset(0, 15) = ""
End If
If OptBacs.Value = True Then
    ActiveCell.Offset(0, 16) = "Bacs"
ElseIf OptChq.Value = True Then
    ActiveCell.Offset(0, 16) = "Cheque"
Else
    ActiveCell.Offset(0, 16) = "Credit Card"
    ActiveCell.Offset(0, 17) = TxtCC
End If
    ActiveCell.Offset(0, 18).Value = CombContract.Value
    ActiveCell.Offset(0, 19).Value = CombContType.Value
    ActiveCell.Offset(0, 20).Value = TxtVehicle
    ActiveCell.Offset(0, 21).Value = TxtDealer
If ChkCom.Value = True Then
    ActiveCell.Offset(0, 22).Value = TxtComAmt
    ActiveCell.Offset(0, 23).Value = TxtComDate
Else
    ActiveCell.Offset(0, 22).Value = 0
    ActiveCell.Offset(0, 23).Value = ""
End If
If OptNew.Value = True Then
    ActiveCell.Offset(0, 24) = "New"
    Else
    ActiveCell.Offset(0, 24) = "Part Exchange"

End If
    ActiveCell.Offset(0, 25).Value = TxtReg
   'Force Status defaulting to first Option (Live)
    ActiveCell.Offset(0, 26).Select
     With Selection.Validation
    .Add xlValidateList, xlValidAlertStop, xlBetween, "=Status"
    .InCellDropdown = True
    ActiveCell = Range("Status")(1)
End With

end sub

I now I still a bit more work to do, but I wanted to get the structure of it working first.

Thanks

 

[SkipVought]

You need to check the SCOPE of the Name name, using Formulas > Defined Names > Name Manager[/ b]. Is the Name range defined for the WORKBOOK?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SkipVought]

Also, why are you using TEXT for your dates in Excel, and furthermore the Short Date is not a date at all?

You do realize that using a REAL NUMERIC DATE with a NUMBER FORMAT will enable you to DISPLAY any sort of visual represation of date that you wish, but it will maintain a REAL NUMERIC DATE wich can actually be used on your sheet, whereas TEXT is virtually useless.

faq68-5827

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[mar050703]

Hi Skip

Yes I have checked the scope of the named range called "Status" within the workbook. If I just do a validate list in a cell, it is fine.

I am really confused by this

Thanks

 

[SkipVought]

Are you ABSOLUTELY sure that Status is the range name and not Name?

Your ORIGINAL post...

.Add xlValidateList, xlValidAlertStop, xlBetween, "=Name"

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[mar050703]

Skip

Yes, totally sure, as "Name" refers to a test file, but "status" is the named range in the actual workbook.

Thanks

 

[SkipVought]

So if you enter status in the Name Box, what happens?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SkipVought]

Fact is, that 1) if your table is a Structured Table and 2) if you assign, in the table, the Data > Validation to that column, then you don't even need to explicitly ADD validation via VBA, simply assign the First value of your Status List!

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[mar050703]

Skip

I want to thank you so very much, that (on my test sheet at least), will save me a huge headache.