Solved: code running too fast?

[white_flag]

Hello

I have a code that will create some validation list:
The problem is: wen the code is running I received error 1004 (error on definition ..etc).. after that.. the pop up message with the error description (press debug), hit F5 again and the code is running fine

but all the time is stopping in this point ..see code bellow:

[vba]
ActiveSheet.Range("B4").Value = Me.ComboBox1.Value
With ActiveSheet.Range("C4").Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=INDEX(INDIRECT($B$4),,1)" 'her is stoping
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

[/vba]
any idea what is wrong?

[white_flag]

"appdelay 3 sec"

I delay the code but the error is there

[Bob Phillips]

Works fine for me in the isolated example that you give. Probably the problem lies in other code in your app.

[white_flag]

Entire code:
[vba]
Sub validation()
Dim r As Range, txt As String
For Each r In ThisWorkbook.Worksheets("Data").Range("A1:A1000")
If Application.International(xlListSeparator) = "," Then
If Not IsEmpty(r) Then txt = txt & "," & r.Value
Else
If Not IsEmpty(r) Then txt = txt & ";" & r.Value
End If
Next
With ThisWorkbook.ActiveSheet.Range("B4")
.Value = Empty
With .validation
.Delete
.Add Type:=xlValidateList, Formula1:=Mid$(txt, 2)
End With
.Select
End With

ActiveSheet.Range("B4").Value = Me.ComboBox1.Value
With ThisWorkbook.ActiveSheet.Range("C4")
.Value = Empty
With .validation
.Delete
.Add Type:=xlValidateList, Formula1:="=INDEX(INDIRECT(B4),,1)"
End With
.Select
End With
With ThisWorkbook.ActiveSheet.Range("D4")
.Value = Empty
With .validation
.Delete
.Add Type:=xlValidateList, Formula1:="=INDEX(INDIRECT(B4),1,)"
End With
.Select
End With
End Sub
[/vba] If I run it step by step(F8)everything is fine. if the code will run automatically (F5) one time error 1004

[Bob Phillips]

Can you post the workbook so I can try it with your A1:A00 data, that looks where the issue might be.

[Bob Phillips]

My guess is that you have a ; separator in your system and this code is the problem

[VBA] For Each r In ThisWorkbook.Worksheets("Data").Range("A1:A1000")
If Application.International(xlListSeparator) = "," Then
If Not IsEmpty(r) Then txt = txt & "," & r.Value
Else
If Not IsEmpty(r) Then txt = txt & ";" & r.Value
End If
Next [/VBA]

Just try

[VBA] For Each r In ThisWorkbook.Worksheets("Data").Range("A1:A1000")
If Not IsEmpty(r) Then txt = txt & "," & r.Value
Next[/VBA]

[white_flag]

I need that part of the code because of "," and ";" separator

at work I have In excel ";" as separator at home "," I think this ";" is acting strange

but if I put like this:
[VBA]
If Application.International(xlListSeparator) = "," Then
With ThisWorkbook.ActiveSheet.Range("D4")
.Value = Empty
With .validation
.Delete
.Add Type:=xlValidateList, Formula1:="=INDEX(INDIRECT(B4),1,)"
End With
.Select
End With
Else
With ThisWorkbook.ActiveSheet.Range("D4")
.Value = Empty
With .validation
.Delete
.Add Type:=xlValidateList, Formula1:="=INDEX(INDIRECT(B4);1" 'error 1004
End With
.Select
End With
End If
[/VBA]

then I have a real error. I will try this code on different machine

[white_flag]

so I change from:
[vba]If Application.International(xlListSeparator) = "," Then[/vba] in
[vba]If Application.International(xlListSeparator) = 5 Then[/vba]
and it is ok (for me doesn't have any logic ..it suppose to work without that condition.. but if it works than it is ok)

[vba]
If Application.International(xlListSeparator) = 5 Then
With ThisWorkbook.ActiveSheet.Range("D4")
.Value = Empty
With .validation
.Delete
.Add Type:=xlValidateList, Formula1:="=INDEX(INDIRECT(B4),1,)"
End With
.Select
End With
Else
With ThisWorkbook.ActiveSheet.Range("D4")
.Value = Empty
With .validation
.Delete
.Add Type:=xlValidateList, Formula1:="=INDEX(INDIRECT(B4);1"
End With
.Select
End With
End If
[/vba]
Bob, thanks you for your time.

ps. here, the weather is pretty nice, I hope that you have a nice weather too.

[Bob Phillips]

My logic was that whatever list separator you are using, Excel will respect that, but VBA uses , regardless (on the basis that VBA is US-centric - don't have a system setup to test thus unfortunately), and when posting back to Excel it is managed by Excel.

Whatever is right, 5 makes no sense at all.


Weather here is okay at the moment, has been grim for a few days now though.

[snb]

I tihink it's Excel's limitation to contain a string as validationlist.
If the validation.formula refences any range there's no problem; when entering a string it's very limited.
You should also remove any validation in a cell before adding a new one (or use it's modify method).