Q2 wants , but the macro wants .

[brecki]

Hi,

I have a problem with loading the quicklist at midnight.

The macro code includes value = 3.1 and as second activity later value = 3.2

When the macro runs, Excel tells me that there is a mistake in my formula because 3.1 and 3.2 are not recognized. It suggests to change it into 31 and 32.

But when I enter 3,1 and 3,2 manually, the commands in Q2 do their work and everyhing is fine.

So I thought I just change the code in the macro from 3.1 to 3,1 and from 3.2 to 3,2 but unfortunately error massages appear as obviously a , instead of a . is not known.

So what can I do to make the macro run ? I do not have any knowledge about macros. I hope you can solve my problem.

Thx Brecki

[GaryRussell]

Can you post the macro code here so we can have a look?

[milfor]

I remember I had those complications with , and . in the beginning as well.
In the Excel options (section: International) change the decimal separator to .
Two applications with different decimal separators can't work together.

[brecki]

Hi milfor,

thx very much, I think this can be the solution for my problem. I will change tonight, as the application is running at the moment.

I will let you know.

[brecki]

Hi Gary,

it is the original code copied by one of your sample sheets.

It is quite strange, because before updating the software, the macro run without any problems. Then suddenly -3.1 was not known any more by Excel, instead -3,1 works (but I can´t get it in the code as a valid command, because vda recognizes the , as a speficic symbol and asks for more parameters).

The actual BA version installed now is (ver. 1.1.0.65v)

I tried to change the symbol settings in Excel as another user suggested here, and that works, but that automatically means, that I have to use . instead of , in any Excel file and that is no acceptable solution because in Germany the official symbol used in any file is a ,

I hope you can correct the code for me in that way, that -3,1 and -3,2 is accepted by the macro as a valid command for cell Q4 (I linked to A3). If that would be possible, I could keep the general setting in my Excel as it is.


Here the codes:

Code on sheet 1:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count = 16 Then
Application.EnableEvents = False
If triggerQuickPickListReload(1) Then
triggerQuickPickListReload(1) = False
Range("Q4").Value = -3.1
triggerFirstMarketSelect(1) = True
Else
If triggerFirstMarketSelect(1) Then
triggerFirstMarketSelect(1) = False
Range("Q4").Value = -5
End If
End If
Application.EnableEvents = True
End If
End Sub


Code on sheet 2:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count = 16 Then
Application.EnableEvents = False
If triggerQuickPickListReload(2) Then
triggerQuickPickListReload(2) = False
Range("Q4").Value = -3.2
triggerFirstMarketSelect(2) = True
Else
If triggerFirstMarketSelect(2) Then
triggerFirstMarketSelect(2) = False
Range("Q4").Value = -5
End If
End If
Application.EnableEvents = True
End If
End Sub


code in "This workbook":

Option Explicit

Private Sub Workbook_Open()
Application.OnTime TimeValue("00:05:00"), "loadQuickPickList"
End Sub


and finally the code in Module 1:

Option Explicit

Const workSheetCount As Integer = 2
Public triggerQuickPickListReload(workSheetCount) As Boolean
Public triggerFirstMarketSelect(workSheetCount) As Boolean

Public Sub loadQuickPickList()
Dim i As Integer
Debug.Print "Trigger quick pick list refresh"
For i = 1 To workSheetCount
triggerQuickPickListReload(i) = True
Next
Application.OnTime TimeValue("00:05:00"), "loadQuickPickList"
End Sub


Thx, Brecki

[milfor]

You could try to replace -3.1 with "-3.1" and -3.2 with "-3.2" in the code.

[brecki]

Oh my God!

Sometimes the solution is that easy. Now it works and my Excel translates "-3.1" into -3,1 when writing to cell Q2.

Thank you so much milfor !!!

Greetings from Germany