Solved

Selecting Textbox in userform using vba in excel

Posted on 2009-07-02
18
631 Views
Last Modified: 2012-05-07
I am trying to select a one textbox of many in a userform after certain conditions are met.  I want to escape from one text box and focus on another in one procedure as a result of this condition.  I have attached the code I am using.  I get an undefined error when using SetFocus function.
Private Sub DATA_2_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
 

    

    

    If DATA_1.Value = "" Then

    Dim Msg, Style, Title, Response, MyString

    Msg = "You have left the previous data cell blank.  If you continue the" _

    & Chr(10) & "capability chart will reflect a skip in the plotted data." _

    & Chr(10) & "" _

    & Chr(10) & "" _

    & Chr(10) & "Continue anyway?" _

    & Chr(10) & ""

    Style = vbYesNo

    Title = "DATA CONFIRMATION"

    Response = MsgBox(Msg, Style, Title)

    If Response = vbYes Then

    If DATA_2.Value = "" Then

    TextBox182.Value = ""

    Else

    TextBox182.Value = 2

    End If

    Else

    DATA_2.Value = ""

    Cancel = True

    

    

'    DATA_1.SetFocus

'    DATA_2.Value = ""

    GoTo ENDPROCEDURE

    End If

    Else

    TextBox182.Value = 2

    End If

    

ENDPROCEDURE:

    Me.DATA_1.SetFocus

Open in new window

0
Comment
Question by:rckrch
  • 9
  • 8
18 Comments
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 500 total points
ID: 24767766
Try this:

ENDPROCEDURE:
    DoEvents
    Me.DATA_1.SetFocus

Kevin
0
 

Author Comment

by:rckrch
ID: 24768007
Thanks, but I still get Unspecified Error at Me.Data_1.SetFocus
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 24768211
I can't recreate the error. Can you post your workbook?

Kevin
0
 

Author Comment

by:rckrch
ID: 24768316
I'll post the entire code for the form.
Private Sub TextBox301_Change()
 

' BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)

    

    If TextBox301.Text = "" Then

    GoTo ENTERDATE

    Else: Me.TextBox301.Text = "" 'TextBox301.Text = "mm/dd/yyyy" Then

    'TextBox301.Text = ""

    End If
 

ENTERDATE:

    TextBox301.ForeColor = RGB(1, 1, 1)

    

End Sub

Private Sub TextBox302_Change()
 

End Sub
 

Private Sub DATA_1_Change()
 

    If DATA_1.Value = "" Then

    TextBox181.Value = ""

    Else

    TextBox181.Value = 1

    End If

    

    

End Sub

Private Sub DATA_2_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
 

    

    

    If DATA_1.Value = "" Then

    Dim Msg, Style, Title, Response, MyString

    Msg = "You have left the previous data cell blank.  If you continue the" _

    & Chr(10) & "capability chart will reflect a skip in the plotted data." _

    & Chr(10) & "" _

    & Chr(10) & "" _

    & Chr(10) & "Continue anyway?" _

    & Chr(10) & ""

    Style = vbYesNo

    Title = "DATA CONFIRMATION"

    Response = MsgBox(Msg, Style, Title)

    If Response = vbYes Then

    If DATA_2.Value = "" Then

    TextBox182.Value = ""

    Else

    TextBox182.Value = 2

    End If

    

    Else

    DATA_2.Value = ""

    Cancel = True

    GoTo ENDPROCEDURE

    End If

    Else

    TextBox182.Value = 2

    End If

    

ENDPROCEDURE:

    DoEvents

    Me.DATA_1.SetFocus

    

    

'Me.CBD.SelStart = 1

'Me.CBD.SelLength = Len(Me.CBD.Value)
 

   

    

    

End Sub

Private Sub DATA_3_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
 

    If DATA_2.Value = "" Then

    Dim Msg, Style, Title, Response, MyString

    Msg = "You have left the previous data cell blank.  If you continue the" _

    & Chr(10) & "capability chart will reflect a skip in the plotted data." _

    & Chr(10) & "" _

    & Chr(10) & "" _

    & Chr(10) & "Continue anyway?" _

    & Chr(10) & ""

    Style = vbYesNo

    Title = "DATA CONFIRMATION"

    Response = MsgBox(Msg, Style, Title)

    If Response = vbYes Then

    

    If DATA_3.Value = "" Then

    TextBox183.Value = ""

    Else

    TextBox183.Value = 3

    End If

    

    Else

    DATA_2.Value = ""

    Cancel = True

    GoTo ENDPROCEDURE

    End If

    Else

    TextBox182.Value = 2

    End If

    

ENDPROCEDURE:

    DoEvents

    Me.DATA_1.SetFocus

    

End Sub

Private Sub DATA_4_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
 

    If DATA_3.Value = "" Then

    Dim Msg, Style, Title, Response, MyString

    Msg = "You have left the previous data cell blank.  If you continue the" _

    & Chr(10) & "capability chart will reflect a skip in the plotted data." _

    & Chr(10) & "" _

    & Chr(10) & "" _

    & Chr(10) & "Continue anyway?" _

    & Chr(10) & ""

    Style = vbYesNo

    Title = "DATA CONFIRMATION"

    Response = MsgBox(Msg, Style, Title)

    If Response = vbYes Then

    

    If DATA_4.Value = "" Then

    TextBox184.Value = ""

    Else

    TextBox184.Value = 4

    End If

    

    Else

    DATA_2.Value = ""

    Cancel = True

    GoTo ENDPROCEDURE

    End If

    Else

    TextBox182.Value = 2

    End If

    

ENDPROCEDURE:

    DoEvents

    Me.DATA_1.SetFocus

    

    

End Sub

Private Sub DATA_5_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
 

    If DATA_4.Value = "" Then

    Dim Msg, Style, Title, Response, MyString

    Msg = "You have left the previous data cell blank.  If you continue the" _

    & Chr(10) & "capability chart will reflect a skip in the plotted data." _

    & Chr(10) & "" _

    & Chr(10) & "" _

    & Chr(10) & "Continue anyway?" _

    & Chr(10) & ""

    Style = vbYesNo

    Title = "DATA CONFIRMATION"

    Response = MsgBox(Msg, Style, Title)

    If Response = vbYes Then
 

    If DATA_5.Value = "" Then

    TextBox185.Value = ""

    Else

    TextBox185.Value = 5

    End If

    

    Else

    DATA_2.Value = ""

    Cancel = True

    GoTo ENDPROCEDURE

    End If

    Else

    TextBox182.Value = 2

    End If

    

ENDPROCEDURE:

    DoEvents

    Me.DATA_1.SetFocus

    

End Sub

Private Sub DATA_6_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
 

    If DATA_5.Value = "" Then

    Dim Msg, Style, Title, Response, MyString

    Msg = "You have left the previous data cell blank.  If you continue the" _

    & Chr(10) & "capability chart will reflect a skip in the plotted data." _

    & Chr(10) & "" _

    & Chr(10) & "" _

    & Chr(10) & "Continue anyway?" _

    & Chr(10) & ""

    Style = vbYesNo

    Title = "DATA CONFIRMATION"

    Response = MsgBox(Msg, Style, Title)

    If Response = vbYes Then
 
 

    If DATA_6.Value = "" Then

    TextBox186.Value = ""

    Else

    TextBox186.Value = 6

    End If

    

    Else

    DATA_2.Value = ""

    Cancel = True

    GoTo ENDPROCEDURE

    End If

    Else

    TextBox182.Value = 2

    End If

    

ENDPROCEDURE:

    DoEvents

    Me.DATA_1.SetFocus

    

End Sub

Private Sub DATA_7_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
 

    If DATA_6.Value = "" Then

    Dim Msg, Style, Title, Response, MyString

    Msg = "You have left the previous data cell blank.  If you continue the" _

    & Chr(10) & "capability chart will reflect a skip in the plotted data." _

    & Chr(10) & "" _

    & Chr(10) & "" _

    & Chr(10) & "Continue anyway?" _

    & Chr(10) & ""

    Style = vbYesNo

    Title = "DATA CONFIRMATION"

    Response = MsgBox(Msg, Style, Title)

    If Response = vbYes Then
 

    If DATA_7.Value = "" Then

    TextBox187.Value = ""

    Else

    TextBox187.Value = 7

    End If

    

    Else

    DATA_2.Value = ""

    Cancel = True

    GoTo ENDPROCEDURE

    End If

    Else

    TextBox182.Value = 2

    End If

    

ENDPROCEDURE:

    DoEvents

    Me.DATA_1.SetFocus

    

End Sub

Private Sub DATA_8_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
 

    If DATA_7.Value = "" Then

    Dim Msg, Style, Title, Response, MyString

    Msg = "You have left the previous data cell blank.  If you continue the" _

    & Chr(10) & "capability chart will reflect a skip in the plotted data." _

    & Chr(10) & "" _

    & Chr(10) & "" _

    & Chr(10) & "Continue anyway?" _

    & Chr(10) & ""

    Style = vbYesNo

    Title = "DATA CONFIRMATION"

    Response = MsgBox(Msg, Style, Title)

    If Response = vbYes Then
 

    If DATA_8.Value = "" Then

    TextBox188.Value = ""

    Else

    TextBox188.Value = 8

    End If

    

    Else

    DATA_2.Value = ""

    Cancel = True

    GoTo ENDPROCEDURE

    End If

    Else

    TextBox182.Value = 2

    End If

    

ENDPROCEDURE:

    DoEvents

    Me.DATA_1.SetFocus

    

End Sub

Private Sub DATA_9_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
 

    If DATA_8.Value = "" Then

    Dim Msg, Style, Title, Response, MyString

    Msg = "You have left the previous data cell blank.  If you continue the" _

    & Chr(10) & "capability chart will reflect a skip in the plotted data." _

    & Chr(10) & "" _

    & Chr(10) & "" _

    & Chr(10) & "Continue anyway?" _

    & Chr(10) & ""

    Style = vbYesNo

    Title = "DATA CONFIRMATION"

    Response = MsgBox(Msg, Style, Title)

    If Response = vbYes Then
 

    If DATA_9.Value = "" Then

    TextBox189.Value = ""

    Else

    TextBox189.Value = 9

    End If

    

    Else

    DATA_2.Value = ""

    Cancel = True

    GoTo ENDPROCEDURE

    End If

    Else

    TextBox182.Value = 2

    End If

    

ENDPROCEDURE:

    DoEvents

    Me.DATA_1.SetFocus

    

End Sub

Private Sub DATA_10_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
 

    If DATA_9.Value = "" Then

    Dim Msg, Style, Title, Response, MyString

    Msg = "You have left the previous data cell blank.  If you continue the" _

    & Chr(10) & "capability chart will reflect a skip in the plotted data." _

    & Chr(10) & "" _

    & Chr(10) & "" _

    & Chr(10) & "Continue anyway?" _

    & Chr(10) & ""

    Style = vbYesNo

    Title = "DATA CONFIRMATION"

    Response = MsgBox(Msg, Style, Title)

    If Response = vbYes Then
 

    If DATA_10.Value = "" Then

    TextBox190.Value = ""

    Else

    TextBox190.Value = 10

    End If

    

    Else

    DATA_2.Value = ""

    Cancel = True

    GoTo ENDPROCEDURE

    End If

    Else

    TextBox182.Value = 2

    End If

    

ENDPROCEDURE:

    DoEvents

    Me.DATA_1.SetFocus

    

End Sub

Private Sub DATA_11_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
 

    If DATA_10.Value = "" Then

    Dim Msg, Style, Title, Response, MyString

    Msg = "You have left the previous data cell blank.  If you continue the" _

    & Chr(10) & "capability chart will reflect a skip in the plotted data." _

    & Chr(10) & "" _

    & Chr(10) & "" _

    & Chr(10) & "Continue anyway?" _

    & Chr(10) & ""

    Style = vbYesNo

    Title = "DATA CONFIRMATION"

    Response = MsgBox(Msg, Style, Title)

    If Response = vbYes Then

    

    If DATA_11.Value = "" Then

    TextBox191.Value = ""

    Else

    TextBox191.Value = 11

    End If

    

    Else

    DATA_2.Value = ""

    Cancel = True

    GoTo ENDPROCEDURE

    End If

    Else

    TextBox182.Value = 2

    End If

    

ENDPROCEDURE:

    DoEvents

    Me.DATA_1.SetFocus

    

End Sub

Private Sub DATA_12_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
 

    If DATA_11.Value = "" Then

    Dim Msg, Style, Title, Response, MyString

    Msg = "You have left the previous data cell blank.  If you continue the" _

    & Chr(10) & "capability chart will reflect a skip in the plotted data." _

    & Chr(10) & "" _

    & Chr(10) & "" _

    & Chr(10) & "Continue anyway?" _

    & Chr(10) & ""

    Style = vbYesNo

    Title = "DATA CONFIRMATION"

    Response = MsgBox(Msg, Style, Title)

    If Response = vbYes Then
 

    If DATA_12.Value = "" Then

    TextBox192.Value = ""

    Else

    TextBox192.Value = 12

    End If

    

    Else

    DATA_2.Value = ""

    Cancel = True

    GoTo ENDPROCEDURE

    End If

    Else

    TextBox182.Value = 2

    End If

    

ENDPROCEDURE:

    DoEvents

    Me.DATA_1.SetFocus

    

End Sub

Private Sub DATA_13_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
 

    If DATA_12.Value = "" Then

    Dim Msg, Style, Title, Response, MyString

    Msg = "You have left the previous data cell blank.  If you continue the" _

    & Chr(10) & "capability chart will reflect a skip in the plotted data." _

    & Chr(10) & "" _

    & Chr(10) & "" _

    & Chr(10) & "Continue anyway?" _

    & Chr(10) & ""

    Style = vbYesNo

    Title = "DATA CONFIRMATION"

    Response = MsgBox(Msg, Style, Title)

    If Response = vbYes Then
 

    If DATA_13.Value = "" Then

    TextBox193.Value = ""

    Else

    TextBox193.Value = 13

    End If

    

    Else

    DATA_2.Value = ""

    Cancel = True

    GoTo ENDPROCEDURE

    End If

    Else

    TextBox182.Value = 2

    End If

    

ENDPROCEDURE:

    DoEvents

    Me.DATA_1.SetFocus

    

End Sub

Private Sub DATA_14_Change()
 

    If DATA_14.Value = "" Then

    TextBox194.Value = ""

    Else

    TextBox194.Value = 14

    End If

    

    

End Sub

Private Sub DATA_15_Change()
 

    If DATA_15.Value = "" Then

    TextBox195.Value = ""

    Else

    TextBox195.Value = 15

    End If

    

    

End Sub

Private Sub DATA_16_Change()
 

    If DATA_16.Value = "" Then

    TextBox196.Value = ""

    Else

    TextBox196.Value = 16

    End If

    

    

End Sub

Private Sub DATA_17_Change()
 

    If DATA_17.Value = "" Then

    TextBox197.Value = ""

    Else

    TextBox197.Value = 17

    End If

    

    

End Sub

Private Sub DATA_18_Change()
 

    If DATA_18.Value = "" Then

    TextBox198.Value = ""

    Else

    TextBox198.Value = 18

    End If

    

    

End Sub

Private Sub DATA_19_Change()
 

    If DATA_19.Value = "" Then

    TextBox199.Value = ""

    Else

    TextBox199.Value = 19

    End If

    

    

End Sub

Private Sub DATA_20_Change()
 

    If DATA_20.Value = "" Then

    TextBox200.Value = ""

    Else

    TextBox200.Value = 20

    End If

    

    

End Sub

Private Sub DATA_21_Change()
 

    If DATA_21.Value = "" Then

    TextBox201.Value = ""

    Else

    TextBox201.Value = 21

    End If

    

    

End Sub

Private Sub DATA_22_Change()
 

    If DATA_22.Value = "" Then

    TextBox202.Value = ""

    Else

    TextBox202.Value = 22

    End If

    

    

End Sub

Private Sub DATA_23_Change()
 

    If DATA_23.Value = "" Then

    TextBox203.Value = ""

    Else

    TextBox203.Value = 23

    End If

    

    

End Sub

Private Sub DATA_24_Change()
 

    If DATA_24.Value = "" Then

    TextBox204.Value = ""

    Else

    TextBox204.Value = 24

    End If

    

    

End Sub

Private Sub DATA_25_Change()
 

    If DATA_25.Value = "" Then

    TextBox205.Value = ""

    Else

    TextBox205.Value = 25

    End If

    

    

End Sub

Private Sub DATA_26_Change()
 

    If DATA_26.Value = "" Then

    TextBox206.Value = ""

    Else

    TextBox206.Value = 26

    End If

    

    

End Sub

Private Sub DATA_27_Change()
 

    If DATA_27.Value = "" Then

    TextBox207.Value = ""

    Else

    TextBox207.Value = 27

    End If

    

    

End Sub

Private Sub DATA_28_Change()
 

    If DATA_28.Value = "" Then

    TextBox208.Value = ""

    Else

    TextBox208.Value = 28

    End If

    

    

End Sub

Private Sub DATA_29_Change()
 

    If DATA_29.Value = "" Then

    TextBox209.Value = ""

    Else

    TextBox209.Value = 29

    End If

    

    

End Sub

Private Sub DATA_30_Change()
 

    If DATA_30.Value = "" Then

    TextBox210.Value = ""

    Else

    TextBox210.Value = 30

    End If

    

    

End Sub

Private Sub DATA_31_Change()
 

    If DATA_31.Value = "" Then

    TextBox211.Value = ""

    Else

    TextBox211.Value = 31

    End If

    

    

End Sub

Private Sub DATA_32_Change()
 

    If DATA_32.Value = "" Then

    TextBox212.Value = ""

    Else

    TextBox212.Value = 32

    End If

    

    

End Sub

Private Sub DATA_33_Change()
 

    If DATA_33.Value = "" Then

    TextBox213.Value = ""

    Else

    TextBox213.Value = 33

    End If

    

    

End Sub

Private Sub DATA_34_Change()
 

    If DATA_34.Value = "" Then

    TextBox214.Value = ""

    Else

    TextBox214.Value = 34

    End If

    

    

End Sub

Private Sub DATA_35_Change()
 

    If DATA_35.Value = "" Then

    TextBox215.Value = ""

    Else

    TextBox215.Value = 35

    End If

    

    

End Sub

Private Sub DATA_36_Change()
 

    If DATA_36.Value = "" Then

    TextBox216.Value = ""

    Else

    TextBox216.Value = 36

    End If

    

    

End Sub

Private Sub DATA_37_Change()
 

    If DATA_37.Value = "" Then

    TextBox217.Value = ""

    Else

    TextBox217.Value = 37

    End If

    

    

End Sub

Private Sub DATA_38_Change()
 

    If DATA_38.Value = "" Then

    TextBox218.Value = ""

    Else

    TextBox218.Value = 38

    End If

    

    

End Sub

Private Sub DATA_39_Change()
 

    If DATA_39.Value = "" Then

    TextBox219.Value = ""

    Else

    TextBox219.Value = 39

    End If

    

    

End Sub

Private Sub DATA_40_Change()
 

    If DATA_40.Value = "" Then

    TextBox220.Value = ""

    Else

    TextBox220.Value = 40

    End If

    

    

End Sub

Private Sub DATA_41_Change()
 

    If DATA_41.Value = "" Then

    TextBox221.Value = ""

    Else

    TextBox221.Value = 41

    End If

    

    

End Sub

Private Sub DATA_42_Change()
 

    If DATA_42.Value = "" Then

    TextBox222.Value = ""

    Else

    TextBox222.Value = 42

    End If

    

    

End Sub

Private Sub DATA_43_Change()
 

    If DATA_43.Value = "" Then

    TextBox223.Value = ""

    Else

    TextBox223.Value = 43

    End If

    

    

End Sub

Private Sub DATA_44_Change()
 

    If DATA_44.Value = "" Then

    TextBox224.Value = ""

    Else

    TextBox224.Value = 44

    End If

    

    

End Sub

Private Sub DATA_45_Change()
 

    If DATA_45.Value = "" Then

    TextBox225.Value = ""

    Else

    TextBox225.Value = 45

    End If

    

    

End Sub

Private Sub DATA_46_Change()
 

    If DATA_46.Value = "" Then

    TextBox226.Value = ""

    Else

    TextBox226.Value = 46

    End If

    

    

End Sub

Private Sub DATA_47_Change()
 

    If DATA_47.Value = "" Then

    TextBox227.Value = ""

    Else

    TextBox227.Value = 47

    End If

    

    

End Sub

Private Sub DATA_48_Change()
 

    If DATA_48.Value = "" Then

    TextBox228.Value = ""

    Else

    TextBox228.Value = 48

    End If

    

    

End Sub

Private Sub DATA_49_Change()
 

    If DATA_49.Value = "" Then

    TextBox229.Value = ""

    Else

    TextBox229.Value = 49

    End If

    

    

End Sub

Private Sub DATA_50_Change()
 

    If DATA_50.Value = "" Then

    TextBox230.Value = ""

    Else

    TextBox230.Value = 50

    End If

    

    

End Sub

Private Sub DATA_51_Change()
 

    If DATA_51.Value = "" Then

    TextBox231.Value = ""

    Else

    TextBox231.Value = 51

    End If

    

    

End Sub

Private Sub DATA_52_Change()
 

    If DATA_52.Value = "" Then

    TextBox232.Value = ""

    Else

    TextBox232.Value = 52

    End If

    

    

End Sub

Private Sub DATA_53_Change()
 

    If DATA_53.Value = "" Then

    TextBox233.Value = ""

    Else

    TextBox233.Value = 53

    End If

    

    

End Sub

Private Sub DATA_54_Change()
 

    If DATA_54.Value = "" Then

    TextBox234.Value = ""

    Else

    TextBox234.Value = 54

    End If

    

    

End Sub

Private Sub DATA_55_Change()
 

    If DATA_55.Value = "" Then

    TextBox235.Value = ""

    Else

    TextBox235.Value = 55

    End If

    

    

End Sub

Private Sub DATA_56_Change()
 

    If DATA_56.Value = "" Then

    TextBox236.Value = ""

    Else

    TextBox236.Value = 56

    End If

    

    

End Sub

Private Sub DATA_57_Change()
 

    If DATA_57.Value = "" Then

    TextBox237.Value = ""

    Else

    TextBox237.Value = 57

    End If

    

    

End Sub

Private Sub DATA_58_Change()
 

    If DATA_58.Value = "" Then

    TextBox238.Value = ""

    Else

    TextBox238.Value = 58

    End If

    

    

End Sub

Private Sub DATA_59_Change()
 

    If DATA_59.Value = "" Then

    TextBox239.Value = ""

    Else

    TextBox239.Value = 59

    End If

    

    

End Sub

Private Sub DATA_60_Change()
 

    If DATA_60.Value = "" Then

    TextBox240.Value = ""

    Else

    TextBox240.Value = 60

    End If

    

    

End Sub

Private Sub DATA_61_Change()
 

    If DATA_61.Value = "" Then

    TextBox241.Value = ""

    Else

    TextBox241.Value = 61

    End If

    

    

End Sub

Private Sub DATA_62_Change()
 

    If DATA_62.Value = "" Then

    TextBox242.Value = ""

    Else

    TextBox242.Value = 62

    End If

    

    

End Sub

Private Sub DATA_63_Change()
 

    If DATA_63.Value = "" Then

    TextBox243.Value = ""

    Else

    TextBox243.Value = 63

    End If

    

    

End Sub

Private Sub DATA_64_Change()
 

    If DATA_64.Value = "" Then

    TextBox244.Value = ""

    Else

    TextBox244.Value = 64

    End If

    

    

End Sub

Private Sub DATA_65_Change()
 

    If DATA_65.Value = "" Then

    TextBox245.Value = ""

    Else

    TextBox245.Value = 65

    End If

    

    

End Sub

Private Sub DATA_66_Change()
 

    If DATA_66.Value = "" Then

    TextBox246.Value = ""

    Else

    TextBox246.Value = 66

    End If

    

    

End Sub

Private Sub DATA_67_Change()
 

    If DATA_67.Value = "" Then

    TextBox247.Value = ""

    Else

    TextBox247.Value = 67

    End If

    

    

End Sub

Private Sub DATA_68_Change()
 

    If DATA_68.Value = "" Then

    TextBox248.Value = ""

    Else

    TextBox248.Value = 68

    End If

    

    

End Sub

Private Sub DATA_69_Change()
 

    If DATA_69.Value = "" Then

    TextBox249.Value = ""

    Else

    TextBox249.Value = 69

    End If

    

    

End Sub

Private Sub DATA_70_Change()
 

    If DATA_70.Value = "" Then

    TextBox250.Value = ""

    Else

    TextBox250.Value = 70

    End If

    

    

End Sub

Private Sub DATA_71_Change()
 

    If DATA_71.Value = "" Then

    TextBox251.Value = ""

    Else

    TextBox251.Value = 71

    End If

    

    

End Sub

Private Sub DATA_72_Change()
 

    If DATA_72.Value = "" Then

    TextBox252.Value = ""

    Else

    TextBox252.Value = 72

    End If

    

    

End Sub

Private Sub DATA_73_Change()
 

    If DATA_73.Value = "" Then

    TextBox253.Value = ""

    Else

    TextBox253.Value = 73

    End If

    

    

End Sub

Private Sub DATA_74_Change()
 

    If DATA_74.Value = "" Then

    TextBox254.Value = ""

    Else

    TextBox254.Value = 74

    End If

    

    

End Sub

Private Sub DATA_75_Change()
 

    If DATA_75.Value = "" Then

    TextBox255.Value = ""

    Else

    TextBox255.Value = 75

    End If

    

    

End Sub

Private Sub DATA_76_Change()
 

    If DATA_76.Value = "" Then

    TextBox256.Value = ""

    Else

    TextBox256.Value = 76

    End If

    

    

End Sub

Private Sub DATA_77_Change()
 

    If DATA_77.Value = "" Then

    TextBox257.Value = ""

    Else

    TextBox257.Value = 77

    End If

    

    

End Sub

Private Sub DATA_78_Change()
 

    If DATA_78.Value = "" Then

    TextBox258.Value = ""

    Else

    TextBox258.Value = 78

    End If

    

    

End Sub

Private Sub DATA_79_Change()
 

    If DATA_79.Value = "" Then

    TextBox259.Value = ""

    Else

    TextBox259.Value = 79

    End If

    

    

End Sub

Private Sub DATA_80_Change()
 

    If DATA_80.Value = "" Then

    TextBox260.Value = ""

    Else

    TextBox260.Value = 80

    End If

    

    

End Sub

Private Sub DATA_81_Change()
 

    If DATA_81.Value = "" Then

    TextBox261.Value = ""

    Else

    TextBox261.Value = 81

    End If

    

    

End Sub

Private Sub DATA_82_Change()
 

    If DATA_82.Value = "" Then

    TextBox262.Value = ""

    Else

    TextBox262.Value = 82

    End If

    

    

End Sub

Private Sub DATA_83_Change()
 

    If DATA_83.Value = "" Then

    TextBox263.Value = ""

    Else

    TextBox263.Value = 83

    End If

    

    

End Sub

Private Sub DATA_84_Change()
 

    If DATA_84.Value = "" Then

    TextBox264.Value = ""

    Else

    TextBox264.Value = 84

    End If

    

    

End Sub

Private Sub DATA_85_Change()
 

    If DATA_85.Value = "" Then

    TextBox265.Value = ""

    Else

    TextBox265.Value = 85

    End If

    

    

End Sub

Private Sub DATA_86_Change()
 

    If DATA_86.Value = "" Then

    TextBox266.Value = ""

    Else

    TextBox266.Value = 86

    End If

    

    

End Sub

Private Sub DATA_87_Change()
 

    If DATA_87.Value = "" Then

    TextBox267.Value = ""

    Else

    TextBox267.Value = 87

    End If

    

    

End Sub

Private Sub DATA_88_Change()
 

    If DATA_88.Value = "" Then

    TextBox268.Value = ""

    Else

    TextBox268.Value = 88

    End If

    

    

End Sub

Private Sub DATA_89_Change()
 

    If DATA_89.Value = "" Then

    TextBox269.Value = ""

    Else

    TextBox269.Value = 89

    End If

    

    

End Sub

Private Sub DATA_90_Change()
 

    If DATA_90.Value = "" Then

    TextBox270.Value = ""

    Else

    TextBox270.Value = 90

    End If

    

    

End Sub

Private Sub DATA_91_Change()
 

    If DATA_91.Value = "" Then

    TextBox271.Value = ""

    Else

    TextBox271.Value = 91

    End If

    

    

End Sub

Private Sub DATA_92_Change()
 

    If DATA_92.Value = "" Then

    TextBox272.Value = ""

    Else

    TextBox272.Value = 92

    End If

    

    

End Sub

Private Sub DATA_93_Change()
 

    If DATA_93.Value = "" Then

    TextBox273.Value = ""

    Else

    TextBox273.Value = 93

    End If

    

    

End Sub

Private Sub DATA_94_Change()
 

    If DATA_94.Value = "" Then

    TextBox274.Value = ""

    Else

    TextBox274.Value = 94

    End If

    

    

End Sub

Private Sub DATA_95_Change()
 

    If DATA_95.Value = "" Then

    TextBox275.Value = ""

    Else

    TextBox275.Value = 95

    End If

    

    

End Sub

Private Sub DATA_96_Change()
 

    If DATA_96.Value = "" Then

    TextBox276.Value = ""

    Else

    TextBox276.Value = 96

    End If

    

    

End Sub

Private Sub DATA_97_Change()
 

    If DATA_97.Value = "" Then

    TextBox277.Value = ""

    Else

    TextBox277.Value = 97

    End If

    

    

End Sub

Private Sub DATA_98_Change()
 

    If DATA_98.Value = "" Then

    TextBox278.Value = ""

    Else

    TextBox278.Value = 98

    End If

    

    

End Sub

Private Sub DATA_99_Change()
 

    If DATA_99.Value = "" Then

    TextBox279.Value = ""

    Else

    TextBox279.Value = 99

    End If

    

    

End Sub

Private Sub DATA_100_Change()
 

    If DATA_100.Value = "" Then

    TextBox280.Value = ""

    Else

    TextBox280.Value = 100

    End If

    

    

End Sub

Private Sub DATA_101_Change()
 

    If DATA_101.Value = "" Then

    TextBox281.Value = ""

    Else

    TextBox281.Value = 101

    End If

    

    

End Sub

Private Sub DATA_102_Change()
 

    If DATA_102.Value = "" Then

    TextBox282.Value = ""

    Else

    TextBox282.Value = 102

    End If

    

    

End Sub

Private Sub DATA_103_Change()
 

    If DATA_103.Value = "" Then

    TextBox283.Value = ""

    Else

    TextBox283.Value = 103

    End If

    

    

End Sub

Private Sub DATA_104_Change()
 

    If DATA_104.Value = "" Then

    TextBox284.Value = ""

    Else

    TextBox284.Value = 104

    End If

    

    

End Sub

Private Sub DATA_105_Change()
 

    If DATA_105.Value = "" Then

    TextBox285.Value = ""

    Else

    TextBox285.Value = 105

    End If

    

    

End Sub

Private Sub DATA_106_Change()
 

    If DATA_106.Value = "" Then

    TextBox286.Value = ""

    Else

    TextBox286.Value = 106

    End If

    

    

End Sub

Private Sub DATA_107_Change()
 

    If DATA_107.Value = "" Then

    TextBox287.Value = ""

    Else

    TextBox287.Value = 107

    End If

    

    

End Sub

Private Sub DATA_108_Change()
 

    If DATA_108.Value = "" Then

    TextBox288.Value = ""

    Else

    TextBox288.Value = 108

    End If

    

    

End Sub

Private Sub DATA_109_Change()
 

    If DATA_109.Value = "" Then

    TextBox289.Value = ""

    Else

    TextBox289.Value = 109

    End If

    

    

End Sub

Private Sub DATA_110_Change()
 

    If DATA_110.Value = "" Then

    TextBox290.Value = ""

    Else

    TextBox290.Value = 110

    End If

    

    

End Sub

Private Sub DATA_111_Change()
 

    If DATA_111.Value = "" Then

    TextBox291.Value = ""

    Else

    TextBox291.Value = 111

    End If

    

    

End Sub

Private Sub DATA_112_Change()
 

    If DATA_112.Value = "" Then

    TextBox292.Value = ""

    Else

    TextBox292.Value = 112

    End If

    

    

End Sub

Private Sub DATA_113_Change()
 

    If DATA_113.Value = "" Then

    TextBox293.Value = ""

    Else

    TextBox293.Value = 113

    End If

    

    

End Sub

Private Sub DATA_114_Change()
 

    If DATA_114.Value = "" Then

    TextBox294.Value = ""

    Else

    TextBox294.Value = 114

    End If

    

    

End Sub

Private Sub DATA_115_Change()
 

    If DATA_115.Value = "" Then

    TextBox295.Value = ""

    Else

    TextBox295.Value = 115

    End If

    

    

End Sub

Private Sub DATA_116_Change()
 

    If DATA_116.Value = "" Then

    TextBox296.Value = ""

    Else

    TextBox296.Value = 116

    End If

    

    

End Sub

Private Sub DATA_117_Change()
 

    If DATA_117.Value = "" Then

    TextBox297.Value = ""

    Else

    TextBox297.Value = 117

    End If

    

    

End Sub

Private Sub DATA_118_Change()
 

    If DATA_118.Value = "" Then

    TextBox298.Value = ""

    Else

    TextBox298.Value = 118

    End If

    

    

End Sub

Private Sub DATA_119_Change()
 

    If DATA_119.Value = "" Then

    TextBox299.Value = ""

    Else

    TextBox299.Value = 119

    End If

    

    

End Sub

Private Sub DATA_120_Change()
 

    If DATA_120.Value = "" Then

    TextBox300.Value = ""

    Else

    TextBox300.Value = 120

    End If

    

    

End Sub
 

Private Sub UserForm_Initialize()
 

    
 

End Sub

Open in new window

0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 24773316
I can't do much without the form. Can you post the workbook? Or copy the form to another workbook and post that workbook?

Kevin
0
 

Author Comment

by:rckrch
ID: 24773904
I transferred to a new workbook and now everything works well.  What could I be doing wrong?  I have attached the new workbook.  On the sheet you will see on the far left side "NEW" - click.  That will bring up the form.  In the red highlighted area of the form enter the current date, press the tab or enter key, the data text box then received the focus.  This is where I have the problem in my full workbook.  The set focus function problem I am having is in the 'txtNEWRUNDATE' procedure.

Could this be some conflict somewhere?

Thanks for your help.

Rick
SETFOCUS-ERROR.xls
0
 

Author Comment

by:rckrch
ID: 24774134
In stepping through the procedure when I click through 'Me.txtNEWDATA1.SetFocus' it steps back to the beginning of the procedure on my full workbook without giving me an error.  It then procedues through the procedure a second time and never focuses on teh txtNEWDATA1 textbox.  It does not do this on the workbook I sent to you.  I have attached the highlighted portion when I step through that step in the procedure.  The code is identical to the one you have.  I simply copied everything.

Please help.

Thanks

Rick
0
 

Author Comment

by:rckrch
ID: 24774173
I changed the tab order so that the text box I want focused for the user is the one tabbed after the date box.  It works this way and steps through the procedure correctly.  However, doesn't this mean that the SetFocus function is not working in this procedure?  I could do the same thing without using the SetFocus function at all.

How am I not using this function correctly in the application?

Thanks for your help.

Rick
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 24775724
I could not reproduce the error with your user form and the code you posted in the question.

Can you reproduce it with the user form you posted?

Kevin
0
 

Author Comment

by:rckrch
ID: 24777185
Yes.  I can reproduce the error by moving the 'txtNEWDATA' tab order from the last one in the order.  If you move it up to an order before the 'txtNEWRUNDATE' textbox it will show the problem.

Rick
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 24779132
I set the tab order of txtNEWDATA to be 127 which forced the tab order of txtNEWRUNDATE to be 128 therefore satisfying your criteria to recreate the problem. I also uncommented the code in the routine DATA_2_Change which had been commented out thus preventing the problem code from even running. The code worked flawlessly and the SetFocus command executed without error when data was entered into Data_2 when Data_1 was empty.

If you are still experiencing an error, I suggest uploading a workbook EXACTLY as it is when the error can be reproduced.

Kevin
0
 

Author Comment

by:rckrch
ID: 24791085
I am attaching a file with the exact code that does not work.  When you enter the date in the 'NEW RUN DATE' box and press the tab key it does not go into next box below for entering the actual data.  It does not work on my computer.

Thanks for your help.

Rick
SUSPECT-FILE---SETFOCUS-ERROR.xls
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 24797762
That was not the problem stated in the original question...which I still can't reproduce.

To solve the problem above, while editing the user form, select the text box txtNEWDATA1 and change the TabIndex property to 326.

Kevin
0
 

Author Comment

by:rckrch
ID: 24798298
I certainly don't claim to be an expert at VBA code.  There is something here I do not understand.  By changing the tab order to the next tab stop in the form I no longer need the setfocus function to get to the desired text box automotically.  What am I missing about the setfocus function?  There is something I am getting wrong with the application of this function I think.  

The last comment I made presents the issue for me in which the text boxes are not in sequential order as you identifed in your previous comment, yet yours worked.  Why?

Please help me understand.

Thanks for your help.

Rick
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 24808707
As you look at the control properties you will see that each control has a TabStop and a TabIndex property. The TabStop determines whether or not the tab key tabs top that control, and the TabIndex property determines the order in which each control is in the tab sequence. All I did above is reset the TabIndex field of the one text entry control such that it followed the other control. You can also select the menu command View->Tab Order to see all of the fields in the tab sequence sorted in the tab order. From this dialog you can shift the controls up and down in the tab order list.

The SetFocus method does just what you think it does and you were, as far as I can tell, using it correctly. As I stated above I was able to get your code to work as desired and without any errors being displayed. Until you can provide a workbook that, as uploaded, causes that error, I will be unable to determine what, if anything, your are doing incorrectly.

Kevin
0
 

Author Comment

by:rckrch
ID: 24810810
The workbook I sent you did demonstrate the problem I was having on my machine.  However, I did do something that solved the problem for me I change the one line of code to include the name of the form:

UserForm35.txtNEWDATA1.SetFocus

This works even without changing the tab properties - I don't really know why.  Could I have some sort of setting different on my machine vs. yours?

Thanks for your time.

Rick
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 24813162
>Could I have some sort of setting different on my machine vs. yours?

Always a possibility ;-)

Kevin
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now