Solved

Selecting Textbox in userform using vba in excel

Posted on 2009-07-02
18
660 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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

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
 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

856 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