C
Claus
Hi
I've added code like below in order to control inputs from users.
It works fine. But i have the following problems:
1) In Denmark where i come from we use , instead of . in numbers and this is
working in the sheets,
but when i load a value like 5,95 from a sheet it turns into 5.95,
making my formulas go crazy.
How do i control this ? I don't see a property like "Format" on a
textbox.
2) How do i control events on controls add by code.
On Exit from a textbox i need to do some calulation to keep the user
updates on price.
3) Any suggestions on how to write back the changes the user made.
Regards,
Claus
CODE:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Dim mycmd As Control, KontrolNavn As String, x As Integer
UserForm2.Controls.Clear
x = 0
While Cells(Target.Row + x + 1, 5).Value <> ""
KontrolNavn = "Label" & Str(x + 1)
Set mycmd = UserForm2.Controls.Add("Forms.Label.1", KontrolNavn,
Visible)
With mycmd
.Left = 10
.Top = 10 + x * 20
.Caption = Str(x + 1) & ". " & Cells(Target.Row + x + 1, 3).Value
.Width = 150
End With
Select Case Cells(Target.Row + x + 1, 4).Value
Case "Tekst", "Tal"
KontrolNavn = "Textbox" & x
Set mycmd = UserForm2.Controls.Add("Forms.Textbox.1", KontrolNavn,
Visible)
With mycmd
.Left = 170
.Top = 5 + x * 20
.Width = 150
.Value = Cells(Target.Row + x + 1, Target.Column).Value
End With
Case "Etiket"
KontrolNavn = "Label" & x
Set mycmd = UserForm2.Controls.Add("Forms.Label.1", KontrolNavn,
Visible)
With mycmd
.Left = 180
.Top = 10 + x * 20
.Width = 150
.Caption = Cells(Target.Row + x + 1, Target.Column).Value
End With
End Select
If InStr(1, Cells(Target.Row + x + 1, 4).Value, ":") > 0 Then
KontrolNavn = "Combobox" & x
Set mycmd = UserForm2.Controls.Add("Forms.combobox.1", KontrolNavn,
Visible)
With mycmd
.Left = 170
.Top = 5 + x * 20
.Width = 150
.RowSource = Cells(Target.Row + x + 1, 4).Value
.Value = Cells(Target.Row + x + 1, Target.Column).Value
End With
End If
x = x + 1
Cells(Target.Row + x, 5).Select
Wend
UserForm2.Show
End Sub
I've added code like below in order to control inputs from users.
It works fine. But i have the following problems:
1) In Denmark where i come from we use , instead of . in numbers and this is
working in the sheets,
but when i load a value like 5,95 from a sheet it turns into 5.95,
making my formulas go crazy.
How do i control this ? I don't see a property like "Format" on a
textbox.
2) How do i control events on controls add by code.
On Exit from a textbox i need to do some calulation to keep the user
updates on price.
3) Any suggestions on how to write back the changes the user made.
Regards,
Claus
CODE:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Dim mycmd As Control, KontrolNavn As String, x As Integer
UserForm2.Controls.Clear
x = 0
While Cells(Target.Row + x + 1, 5).Value <> ""
KontrolNavn = "Label" & Str(x + 1)
Set mycmd = UserForm2.Controls.Add("Forms.Label.1", KontrolNavn,
Visible)
With mycmd
.Left = 10
.Top = 10 + x * 20
.Caption = Str(x + 1) & ". " & Cells(Target.Row + x + 1, 3).Value
.Width = 150
End With
Select Case Cells(Target.Row + x + 1, 4).Value
Case "Tekst", "Tal"
KontrolNavn = "Textbox" & x
Set mycmd = UserForm2.Controls.Add("Forms.Textbox.1", KontrolNavn,
Visible)
With mycmd
.Left = 170
.Top = 5 + x * 20
.Width = 150
.Value = Cells(Target.Row + x + 1, Target.Column).Value
End With
Case "Etiket"
KontrolNavn = "Label" & x
Set mycmd = UserForm2.Controls.Add("Forms.Label.1", KontrolNavn,
Visible)
With mycmd
.Left = 180
.Top = 10 + x * 20
.Width = 150
.Caption = Cells(Target.Row + x + 1, Target.Column).Value
End With
End Select
If InStr(1, Cells(Target.Row + x + 1, 4).Value, ":") > 0 Then
KontrolNavn = "Combobox" & x
Set mycmd = UserForm2.Controls.Add("Forms.combobox.1", KontrolNavn,
Visible)
With mycmd
.Left = 170
.Top = 5 + x * 20
.Width = 150
.RowSource = Cells(Target.Row + x + 1, 4).Value
.Value = Cells(Target.Row + x + 1, Target.Column).Value
End With
End If
x = x + 1
Cells(Target.Row + x, 5).Select
Wend
UserForm2.Show
End Sub