Dynamic userform. Problem with format, events and returning values
 

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

Dynamic userform. Problem with format, events and returning values
 

Claus,

Use
Textbox.Text = CStr(Mycell.Value) and
Mycell.value = CDbl(Textbox.text)

to support regional formatting.

For writing run-time code you need the VBA Extensibility(VBIDE) library
to write code to your forms code module. This is not advisable!

The best way is to use

Public WithEvents txt As MSForms.TextBox in your Forms module and use
the Keypress event to test for Enter/Tab(Exit event is not available)

Dm Unseen

Dynamic userform. Problem with format, events and returning values
 

Thank you, i've got it working

Appreciate your help

Regards,
Claus

"DM Unseen" skrev i en meddelelse
oups.com...
Claus,

Use
Textbox.Text = CStr(Mycell.Value) and
Mycell.value = CDbl(Textbox.text)

to support regional formatting.

For writing run-time code you need the VBA Extensibility(VBIDE) library
to write code to your forms code module. This is not advisable!

The best way is to use

Public WithEvents txt As MSForms.TextBox in your Forms module and use
the Keypress event to test for Enter/Tab(Exit event is not available)

Dm Unseen