userform buttons

[rsimon36555]

I have two buttons on my userform with numerous textboxes. When I first run my userform, the buttons don't work on the first textbox. After I pass that box, the buttons work on all the other boxes. What gives? What makes that first textbox so special? It is setup that same as all the other textboxes.

Any help will be appreciated...Thanks!

_________________


[ This Message was edited by: rsimon36555 on 2002-05-20 12:48 ]

[Von Pookie]

Could you post the code you are using for the userform?

[Ivan F Moala]

On 2002-05-20 12:47, rsimon36555 wrote:
I have two buttons on my userform with numerous textboxes. When I first run my userform, the buttons don't work on the first textbox. After I pass that box, the buttons work on all the other boxes. What gives? What makes that first textbox so special? It is setup that same as all the other textboxes.

Any help will be appreciated...Thanks!

_________________


[ This Message was edited by: rsimon36555 on 2002-05-20 12:48 ]

You may not have referenced the Text box properly.....what is your code ?

[rsimon36555]

Here is the code. The starting textbox is ItemDescCode. It is here that neither button works. After I make an entry, and I'm in the next field, the buttons work.

Thanks for looking!



' Define Global Variables

Dim done As Boolean, ContinueRun As Boolean, SkipCost As Boolean
Dim LastRow As Integer, LastColumn As Integer, CurMLRow As Integer
Dim LivoniaTon As Integer, GrandRapidsTon As Integer, TwinsburgTon As Integer, BellevilleTon As Integer
Dim LivoniaPrice As Single, GrandRapidsPrice As Single, TwinsburgPrice As Single, BellevillePrice As Single
'****************************************************************************************
Public Function FindLastRow(SheetID As String) As Integer

With Sheets(SheetID)
FindLastRow = WorksheetFunction.CountA(.Columns(1))
End With

End Function
'****************************************************************************************
Public Function FindLastColumn(SheetID As String) As Integer

With Sheets(SheetID)
FindLastColumn = WorksheetFunction.CountA(.Rows(1))
End With

End Function
'****************************************************************************************
Private Sub CommandButton1_Click()
UserForm1.Hide
Unload UserForm1
Worksheets("masterlist").Select
ActiveWindow.WindowState = xlMaximized
End Sub

Private Sub CommandButton2_Click()
FinishProcess
End Sub
Private Sub FinishProcess()

Dim ctl As Control

Worksheets("masterlist").Cells(CurMLRow, 11) = LivoniaTon + GrandRapidsTon + TwinsburgTon + BellevilleTon
Worksheets("masterlist").Cells(CurMLRow, 11).NumberFormat = "###.00"

For Each ctl In UserForm1.Controls
If TypeName(ctl) = "TextBox" Then
If ctl.Name <> "ItemDescCode" Then ' We'll clear it when we get into the routine
ctl.Text = ""
End If
End If
Next ctl
End Sub
'****************************************************************************************
Private Sub ItemDescCode_Enter()

UserForm1.ItemDescCode.Value = ""
UserForm1.Prompt.Value = "Enter an item description"

End Sub
'****************************************************************************************
Private Sub ItemDescCode_AfterUpdate()

Dim ItemDescCode As String
Dim Rfound As Range, iLoop As Integer

ItemDescCode = UserForm1.ItemDescCode.Value

Set Rfound = Range("A1")
Set Rfound = Range(Cells(1, 1), Cells(LastRow, LastColumn)).Find(what:=ItemDescCode, after:=Rfound, lookat:=xlWhole, MatchCase:=False)

If Rfound Is Nothing Then
MsgBox "Item entered not found. Reenter your last item again."
ContinueRun = False
Else
If Rfound.Value = UCase$(ItemDescCode) Then
UserForm1.ItemForm.Value = Rfound.Offset(0, 1).Value
CurMLRow = FindLastRow("masterlist") + 1
Worksheets("masterlist").Cells(CurMLRow, 1).Value = Rfound.Value

Worksheets("MasterList").Cells(CurMLRow, 2).Value = Rfound.Offset(0, 1).Value
Columns(2).Select
Selection.Columns(2).Text
Columns(2).AutoFit

Cells(CurMLRow, 12).Text
Worksheets("MasterList").Cells(CurMLRow, 12).Value = Rfound.Offset(0, 2).Value
Columns(12).Select
Columns(12).AutoFit
' Worksheets("masterlist").Cells(CurMLRow, 12).NumberFormat = "####################"

ContinueRun = True
InitializeAllFields
Else
MsgBox "Item entered not found. Reenter your last item again."
ContinueRun = False
End If
End If

End Sub
'****************************************************************************************
Private Sub ItemDescCode_Exit(ByVal Cancel As MSForms.ReturnBoolean)

If Not ContinueRun Then
Cancel = True
End If

End Sub
'****************************************************************************************
Sub InitializeAllFields()

LivoniaTon = 0
LivoniaPrice = 0
GrandRapidsTon = 0
GrandRapidsPrice = 0
TwinsburgTon = 0
TwinsburgPrice = 0
BellevilleTon = 0
BellevillePrice = 0

End Sub
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
'****************************************************************************************
Private Sub LivoniaCost_enter()

UserForm1.Prompt.Value = "Enter the item purchase price (CWT)"

If LivoniaTon = 0 Then
' MsgBox "Moving on..."
UserForm1.GrandRapidsTons.SetFocus
End If

End Sub
'****************************************************************************************
Private Sub LivoniaCost_afterupdate()

If (IsNumeric(UserForm1.LivoniaCost.Value)) Then
LivoniaPrice = Val(UserForm1.LivoniaCost.Value)
UserForm1.LivoniaCost.Value = Format$(LivoniaPrice, "###.00")
Worksheets("masterlist").Cells(CurMLRow, 4) = LivoniaPrice
Worksheets("masterlist").Cells(CurMLRow, 4).NumberFormat = "###.00"
Else: MsgBox "Cost must be a numeric value"
ContinueRun = False
End If

End Sub
'****************************************************************************************
Private Sub LivoniaCost_Error(ByVal Number As Integer, ByVal Description As MSForms.ReturnString, ByVal SCode As Long, ByVal Source As String, ByVal HelpFile As String, ByVal HelpContext As Long, ByVal CancelDisplay As MSForms.ReturnBoolean)

If Not ContinueRun Then
Cancel = True
End If

End Sub
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
'****************************************************************************************
Private Sub LivoniaTons_Enter()

UserForm1.Prompt.Value = "Enter the number of tons to order for Livonia"

End Sub
'****************************************************************************************
Private Sub LivoniaTons_AfterUpdate()

If (Len(UserForm1.LivoniaTons.Value) > 0) Then
If IsNumeric(UserForm1.LivoniaTons.Value) Then
LivoniaTon = Val(UserForm1.LivoniaTons.Value)

Select Case LivoniaTon
Case Is > 0
Worksheets("masterlist").Cells(CurMLRow, 3).Value = LivoniaTon
Worksheets("masterlist").Cells(CurMLRow, 3).NumberFormat = "####"
Case 0
UserForm1.GrandRapidsTons.SetFocus

Case Else
MsgBox "Enter only positive numbers or 0 (zero) as a value or leave blank"
ContinueRun = False
End Select
Else
MsgBox "Enter a number as a value or leave blank"
ContinueRun = False
End If
Else
UserForm1.GrandRapidsTons.SetFocus
End If

End Sub
'****************************************************************************************
Private Sub LivoniaTons_Exit(ByVal Cancel As MSForms.ReturnBoolean)

If Not ContinueRun Then
Cancel = True
End If
End Sub
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
'****************************************************************************************
Private Sub GrandRapidsTons_Enter()

UserForm1.Prompt.Value = "Enter the number of tons to order for Grand Rapids"

End Sub
'****************************************************************************************
Private Sub GrandRapidsTons_AfterUpdate()

If (Len(UserForm1.GrandRapidsTons.Value) > 0) Then
If IsNumeric(UserForm1.GrandRapidsTons.Value) Then
GrandRapidsTon = Val(UserForm1.GrandRapidsTons.Value)

Select Case GrandRapidsTon
Case Is > 0
Worksheets("masterlist").Cells(CurMLRow, 5).Value = GrandRapidsTon
Worksheets("masterlist").Cells(CurMLRow, 5).NumberFormat = "####"

Case 0
UserForm1.TwinsburgTons.SetFocus

Case Else
MsgBox "Enter only positive numbers or 0 (zero) as a value or leave blank"
ContinueRun = False
End Select
Else
MsgBox "Enter a number as a value or leave blank"
ContinueRun = False
End If
Else
UserForm1.TwinsburgTons.SetFocus
End If

End Sub
'****************************************************************************************
Private Sub GrandRapidsTons_Exit(ByVal Cancel As MSForms.ReturnBoolean)

If Not ContinueRun Then
Cancel = True
End If
End Sub
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
'****************************************************************************************
Private Sub GrandRapidsCost_enter()

UserForm1.Prompt.Value = "Enter the item purchase price (CWT)"

If GrandRapidsTon = 0 Then
' MsgBox "Moving on..."
UserForm1.TwinsburgTons.SetFocus
End If

End Sub
'****************************************************************************************
Private Sub GrandRapidsCost_afterupdate()

If (IsNumeric(UserForm1.GrandRapidsCost.Value)) Then
GrandRapidsPrice = Val(UserForm1.GrandRapidsCost.Value)
UserForm1.GrandRapidsCost.Value = Format$(GrandRapidsPrice, "###.00")
Worksheets("masterlist").Cells(CurMLRow, 6) = GrandRapidsPrice
Worksheets("masterlist").Cells(CurMLRow, 6).NumberFormat = "###.00"
Else
MsgBox "Cost must be a numeric value"
ContinueRun = False
End If

End Sub
'****************************************************************************************
Private Sub GrandRapidsCost_Error(ByVal Number As Integer, ByVal Description As MSForms.ReturnString, ByVal SCode As Long, ByVal Source As String, ByVal HelpFile As String, ByVal HelpContext As Long, ByVal CancelDisplay As MSForms.ReturnBoolean)

If Not ContinueRun Then
Cancel = True
End If

End Sub
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
'****************************************************************************************
Private Sub TwinsburgTons_Enter()

UserForm1.Prompt.Value = "Enter the number of tons to order for Twinsburg"

End Sub
'****************************************************************************************
Private Sub TwinsburgTons_AfterUpdate()

If (Len(UserForm1.TwinsburgTons.Value) > 0) Then
If IsNumeric(UserForm1.TwinsburgTons.Value) Then
TwinsburgTon = Val(UserForm1.TwinsburgTons.Value)

Select Case TwinsburgTon
Case Is > 0
Worksheets("masterlist").Cells(CurMLRow, 7).Value = TwinsburgTon
Worksheets("masterlist").Cells(CurMLRow, 7).NumberFormat = "####"
Case 0
UserForm1.TwinsburgTons.SetFocus

Case Else
MsgBox "Enter only positive numbers or 0 (zero) as a value or leave blank"
ContinueRun = False
End Select
Else
MsgBox "Enter a number as a value or leave blank"
ContinueRun = False
End If
Else
UserForm1.TwinsburgTons.SetFocus
End If

End Sub
'****************************************************************************************
Private Sub TwinsburgTons_Exit(ByVal Cancel As MSForms.ReturnBoolean)

If Not ContinueRun Then
Cancel = True
End If
End Sub
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
'****************************************************************************************
Private Sub TwinsburgCost_enter()

UserForm1.Prompt.Value = "Enter the item purchase price (CWT)"

If TwinsburgTon = 0 Then
' MsgBox "Moving on..."
UserForm1.BellevilleTons.SetFocus
End If

End Sub
'****************************************************************************************
Private Sub TwinsburgCost_afterupdate()

If (IsNumeric(UserForm1.TwinsburgCost.Value)) Then
TwinsburgPrice = Val(UserForm1.TwinsburgCost.Value)
UserForm1.TwinsburgCost.Value = Format$(TwinsburgPrice, "###.00")
Worksheets("masterlist").Cells(CurMLRow, = TwinsburgPrice
Worksheets("masterlist").Cells(CurMLRow, .NumberFormat = "###.00"
Else
MsgBox "Cost must be a numeric value"
ContinueRun = False
End If

End Sub
'****************************************************************************************
Private Sub TwinsburgCost_Error(ByVal Number As Integer, ByVal Description As MSForms.ReturnString, ByVal SCode As Long, ByVal Source As String, ByVal HelpFile As String, ByVal HelpContext As Long, ByVal CancelDisplay As MSForms.ReturnBoolean)

If Not ContinueRun Then
Cancel = True
End If

End Sub
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
'****************************************************************************************
Private Sub BellevilleCost_enter()

UserForm1.Prompt.Value = "Enter the item purchase price (CWT)"

If BellevilleTon = 0 Then
' MsgBox "Moving on..."
' UserForm1.CommandButton2.SetFocus
FinishProcess
End If

End Sub
'****************************************************************************************
Private Sub BellevilleCost_afterupdate()

If (IsNumeric(UserForm1.BellevilleCost.Value)) Then
BellevillePrice = Val(UserForm1.BellevilleCost.Value)
UserForm1.BellevilleCost.Value = Format$(BellevillePrice, "###.00")
Worksheets("masterlist").Cells(CurMLRow, 10) = BellevillePrice
Worksheets("masterlist").Cells(CurMLRow, 10).NumberFormat = "###.00"
FinishProcess
Else: MsgBox "Cost must be a numeric value"
ContinueRun = False
End If

End Sub
'****************************************************************************************
Private Sub BellevilleCost_Error(ByVal Number As Integer, ByVal Description As MSForms.ReturnString, ByVal SCode As Long, ByVal Source As String, ByVal HelpFile As String, ByVal HelpContext As Long, ByVal CancelDisplay As MSForms.ReturnBoolean)

If Not ContinueRun Then
Cancel = True
End If

End Sub
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
'****************************************************************************************
Private Sub BellevilleTons_Enter()

UserForm1.Prompt.Value = "Enter the number of tons to order for Belleville"

End Sub
'****************************************************************************************
Private Sub BellevilleTons_AfterUpdate()

If (Len(UserForm1.BellevilleTons.Value) > 0) Then
If IsNumeric(UserForm1.BellevilleTons.Value) Then
BellevilleTon = Val(UserForm1.BellevilleTons.Value)

Select Case BellevilleTon
Case Is > 0
Worksheets("masterlist").Cells(CurMLRow, 9).Value = BellevilleTon
Worksheets("masterlist").Cells(CurMLRow, 9).NumberFormat = "####"

Case 0
FinishProcess
' UserForm1.CommandButton2.SetFocus

Case Else
MsgBox "Enter only positive numbers or 0 (zero) as a value or leave blank"
ContinueRun = False
End Select
Else
MsgBox "Enter a number as a value or leave blank"
ContinueRun = False
End If
Else
' UserForm1.CommandButton2.SetFocus
FinishProcess
End If

End Sub
'****************************************************************************************
Private Sub BellevilleTons_Exit(ByVal Cancel As MSForms.ReturnBoolean)

If Not ContinueRun Then
Cancel = True
End If

End Sub

Private Sub UserForm_Activate()

Dim itemsheet As String

itemsheet = Worksheets("ItemList").Name
Worksheets(itemsheet).Select
ActiveWindow.WindowState = xlMinimized

LastRow = FindLastRow(itemsheet)
LastColumn = FindLastColumn(itemsheet)

End Sub

Private Sub UserForm_Deactivate()

Worksheets("masterlist").Select
ActiveWindow.WindowState = xlMaximized

End Sub

[Ivan F Moala]

Hi Robert...which textbox is not functioning.
From your discription I'm assuming that
@ the press of commandbutton2 your textbox
(which one) does not update ??
please elaborate.

[rsimon36555]

The textbox is ItemDescCode (1st textbox). Neither button works. After leaving this textbox, both buttons work.

After further debugging, it appears that when I'm in the ItemDescCode textbox, when I hit one of the buttons, the routine immediately goes to the _exit routine where it is cycled back to the originating routine (like it never left and did anything).

What the question is now is: How can I tell what button was pressed from inside the _exit routine? I could then take the appropiate action from there.
_________________
Robert Simon
Contractors Steel Company
Novice VBA Programmer

[ This Message was edited by: rsimon36555 on 2002-05-21 13:10 ]