D
don H. Lee
Hi newsgroup
I have a little problem with my excel app (Excel 2000, 2003)
For data entering reasons I am using a Form. On this Form I use Textboxes
(also Comboboxes) which are direcly linked to a cell (controlsource) and
other ones which I fill with data throught code on the events Activate and
QueryClose. This textboxes are formated by event exit (later more).
Here are some code snippets I am using for better understanding:
1. I made a Module where I host some functions to format the text in the
textbox:
Public Function FormatAsPoliceNumber(Num As Variant) As String
FormatAsPoliceNumber = Format(Num, "000000.000")
End Function
Public Function FormatAsRisikoNumber(Num As Variant) As String
FormatAsRisikoNumber = Format(Num, "0000")
End Function
2.In the exit-event of some textboxes I format the text like code below:
Private Sub txtPolicenDatenPNR_UVG_Exit(ByVal Cancel As
MSForms.ReturnBoolean)
txtPolicenDatenPNR_UVG.Text =
FormatAsPoliceNumber(txtPolicenDatenPNR_UVG.Text)
End Sub
Private Sub txtPolicenDatenPNR_UVGE_Exit(ByVal Cancel As
MSForms.ReturnBoolean)
txtPolicenDatenPNR_UVGE.Text =
FormatAsPoliceNumber(txtPolicenDatenPNR_UVGE.Text)
End Sub
Private Sub txtBetriebRisikonNr_Exit(ByVal Cancel As MSForms.ReturnBoolean)
txtBetriebRisikonNr.Text = FormatAsRisikoNumber(txtBetriebRisikonNr.Text)
End Sub
Private Sub txtUVG_E_AbwTar_RabGrosse_Exit(ByVal Cancel As
MSForms.ReturnBoolean)
txtUVG_E_AbwTar_RabGrosse = FormatAsNumberFull(txtUVG_E_AbwTar_RabGrosse)
End Sub
3. In the Activate-Event of the Form I fill the textboxes with values of the
cells:
Private Sub UserForm_Activate()
txtPolicenDatenPNR_UVG.Text = Worksheets("Antrag").Range("p12").Value
txtPolicenDatenPNR_UVGE.Text = Worksheets("Antrag").Range("p17").Value
txtBetriebRisikonNr.Text = Worksheets("Risiko_Nr.").Range("B1").Value
txtUVGAbwTarVWKBU.Text = Worksheets("Berechnung_UVG").Range("h3").Value
txtUVGAbwTarVWKNBU.Text = Worksheets("Berechnung_UVG").Range("h8").Value
end sub
4. On the QueryClose event the other way
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
Worksheets("Antrag").Range("p12").Value = txtPolicenDatenPNR_UVG.Text
Worksheets("Antrag").Range("p17").Value = txtPolicenDatenPNR_UVGE.Text
Worksheets("Risiko_Nr.").Range("B1").Value = txtBetriebRisikonNr.Text
Worksheets("Berechnung_UVG").Range("h3").Value = txtUVGAbwTarVWKBU.Text
Worksheets("Berechnung_UVG").Range("h8").Value = txtUVGAbwTarVWKNBU.Text
Worksheets("Berechnung_UVG").Range("h13").Value =
txtUVGAbwTarVWKFreiVers.Text
Worksheets("Berechnung_UVG").Range("e3").Value =
txtUVGAbwTarErfTarifStufe.Text
end sub
This are only snippets. There are about 30 textboxes I am handling like this
and about 40 textboxes which are directly connected with the cells.
Now: When I open and directly close the form Excel crashes in cause of a
unknown error
The same happens, when I jump from one textbox to another.
(For testing reasons I removed the whole code, an execl didn't crash!)
Do someone has an idea on how to handle this?
Thanks for the help.
Regards
D.H. Lee
I have a little problem with my excel app (Excel 2000, 2003)
For data entering reasons I am using a Form. On this Form I use Textboxes
(also Comboboxes) which are direcly linked to a cell (controlsource) and
other ones which I fill with data throught code on the events Activate and
QueryClose. This textboxes are formated by event exit (later more).
Here are some code snippets I am using for better understanding:
1. I made a Module where I host some functions to format the text in the
textbox:
Public Function FormatAsPoliceNumber(Num As Variant) As String
FormatAsPoliceNumber = Format(Num, "000000.000")
End Function
Public Function FormatAsRisikoNumber(Num As Variant) As String
FormatAsRisikoNumber = Format(Num, "0000")
End Function
2.In the exit-event of some textboxes I format the text like code below:
Private Sub txtPolicenDatenPNR_UVG_Exit(ByVal Cancel As
MSForms.ReturnBoolean)
txtPolicenDatenPNR_UVG.Text =
FormatAsPoliceNumber(txtPolicenDatenPNR_UVG.Text)
End Sub
Private Sub txtPolicenDatenPNR_UVGE_Exit(ByVal Cancel As
MSForms.ReturnBoolean)
txtPolicenDatenPNR_UVGE.Text =
FormatAsPoliceNumber(txtPolicenDatenPNR_UVGE.Text)
End Sub
Private Sub txtBetriebRisikonNr_Exit(ByVal Cancel As MSForms.ReturnBoolean)
txtBetriebRisikonNr.Text = FormatAsRisikoNumber(txtBetriebRisikonNr.Text)
End Sub
Private Sub txtUVG_E_AbwTar_RabGrosse_Exit(ByVal Cancel As
MSForms.ReturnBoolean)
txtUVG_E_AbwTar_RabGrosse = FormatAsNumberFull(txtUVG_E_AbwTar_RabGrosse)
End Sub
3. In the Activate-Event of the Form I fill the textboxes with values of the
cells:
Private Sub UserForm_Activate()
txtPolicenDatenPNR_UVG.Text = Worksheets("Antrag").Range("p12").Value
txtPolicenDatenPNR_UVGE.Text = Worksheets("Antrag").Range("p17").Value
txtBetriebRisikonNr.Text = Worksheets("Risiko_Nr.").Range("B1").Value
txtUVGAbwTarVWKBU.Text = Worksheets("Berechnung_UVG").Range("h3").Value
txtUVGAbwTarVWKNBU.Text = Worksheets("Berechnung_UVG").Range("h8").Value
end sub
4. On the QueryClose event the other way
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
Worksheets("Antrag").Range("p12").Value = txtPolicenDatenPNR_UVG.Text
Worksheets("Antrag").Range("p17").Value = txtPolicenDatenPNR_UVGE.Text
Worksheets("Risiko_Nr.").Range("B1").Value = txtBetriebRisikonNr.Text
Worksheets("Berechnung_UVG").Range("h3").Value = txtUVGAbwTarVWKBU.Text
Worksheets("Berechnung_UVG").Range("h8").Value = txtUVGAbwTarVWKNBU.Text
Worksheets("Berechnung_UVG").Range("h13").Value =
txtUVGAbwTarVWKFreiVers.Text
Worksheets("Berechnung_UVG").Range("e3").Value =
txtUVGAbwTarErfTarifStufe.Text
end sub
This are only snippets. There are about 30 textboxes I am handling like this
and about 40 textboxes which are directly connected with the cells.
Now: When I open and directly close the form Excel crashes in cause of a
unknown error
The same happens, when I jump from one textbox to another.
(For testing reasons I removed the whole code, an execl didn't crash!)
Do someone has an idea on how to handle this?
Thanks for the help.
Regards
D.H. Lee