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 don't have a guess...

But if I had to debug this kind of problem, then next thing I would do is create
the userform in another workbook to see if crashed there.



"don H. Lee" wrote:

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

--

Dave Peterson