PDA

View Full Version : Solved: code running too fast?



white_flag
09-27-2012, 01:51 AM
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:


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


any idea what is wrong?

white_flag
09-27-2012, 02:07 AM
"appdelay 3 sec"

I delay the code but the error is there

Bob Phillips
09-27-2012, 03:33 AM
Works fine for me in the isolated example that you give. Probably the problem lies in other code in your app.

white_flag
09-27-2012, 04:05 AM
Entire code:

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
If I run it step by step(F8)everything is fine. if the code will run automatically (F5) one time error 1004

Bob Phillips
09-27-2012, 04:51 AM
Can you post the workbook so I can try it with your A1:A00 data, that looks where the issue might be.

Bob Phillips
09-27-2012, 04:53 AM
My guess is that you have a ; separator in your system and this code is the problem

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

Just try

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

white_flag
09-27-2012, 05:13 AM
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:

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


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

white_flag
09-27-2012, 05:46 AM
so I change from:
If Application.International(xlListSeparator) = "," Then in
If Application.International(xlListSeparator) = 5 Then
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)


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

Bob, thanks you for your time.

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

Bob Phillips
09-27-2012, 08:25 AM
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
09-27-2012, 09:16 AM
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).