After transfering data from a userform one sheet becomes hightlighted

PMRetired2012

New Member
Joined
Aug 6, 2019
Messages
27
The problem is when i use a userform to transfer data to a worksheet that worksheet becomes highlighted. the problem is this started at row 54 and continues on the rest of worksheet as i add data from the userform this problem didnt happen in the previous 53 rows. What i have to do to get the worksheet un highlighted is click the exit form button and them click anywhere on the worksheet and it becomes un highlighted.
My question why did it start at row 54 and not other rows. something has be clicked or someting form line 54 on.

Need Help !
Thanks
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,759
Office Version
2007
Platform
Windows
You can put all your code here to review it.
 

PMRetired2012

New Member
Joined
Aug 6, 2019
Messages
27
The problem of highlighting is happen on the Address Master sheet. there are 7 worksheets all together.

Thanks




Code:
Private Sub CommandButton1_Click()
 On Error GoTo 1
    Sheet = ComboBox1.Text
    If Sheet = "" Then
        MsgBox "Select Class", vbInformation, "Error"
    Exit Sub
    End If
    
  'Update each class sheet Code
  
    Sheets(Sheet).Select
    Set findBlank = Range("A1:A32").Find(What:="", lookat:=xlWhole)
        findBlank.Select
    ActiveCell.Value = TextBox1.Text
    ActiveCell.Offset(0, 1).Value = TextBox2.Text
    ActiveCell.Offset(0, 2).Value = ComboBox2.Text
    ActiveCell.Offset(0, 3).Value = TextBox3.Text
    ActiveCell.Offset(0, 4).Value = TextBox4.Text
    ActiveCell.Offset(0, 5).Value = TextBox5.Text
    ActiveCell.Offset(0, 6).Value = ComboBox3.Text
    ActiveCell.Offset(0, 7).Value = TextBox6.Text
    ActiveCell.Offset(0, 8).Value = ComboBox4.Text
    ActiveCell.Offset(0, 9).Value = TextBox7.Text
    ActiveCell.Offset(0, 10).Value = TextBoX8.Text
    ActiveCell.Offset(0, 11).Value = TextBox9.Text
    ActiveCell.Offset(0, 12).Value = TextBox10.Text
    ActiveCell.Offset(0, 13).Value = TextBox11.Text
    
    If OptionButton1 = True Then
        ActiveCell.Offset(0, 14).Value = "Y"
    ElseIf OptionButton2 = True Then
        ActiveCell.Offset(0, 14).Value = "N"
    End If
    
    ActiveCell.Offset(0, 15).Value = TextBox12.Text
    


    
    
    
 'Update Master Sheet
     If Sheet = "MASTER SHEET" Then
        MsgBox "Select Class", vbInformation, "Error"
    Exit Sub
    End If
    
    Sheets("MASTER SHEET").Select
    Set findBlank = Range("A1:A90").Find(What:="", lookat:=xlWhole)
        findBlank.Select
    ActiveCell.Value = TextBox1.Text
    ActiveCell.Offset(0, 1).Value = TextBox2.Text
    ActiveCell.Offset(0, 2).Value = ComboBox2.Text
    ActiveCell.Offset(0, 3).Value = TextBox3.Text
    ActiveCell.Offset(0, 4).Value = TextBox4.Text
    ActiveCell.Offset(0, 5).Value = TextBox5.Text
    ActiveCell.Offset(0, 6).Value = ComboBox3.Text
    ActiveCell.Offset(0, 7).Value = TextBox6.Text
    ActiveCell.Offset(0, 8).Value = ComboBox4.Text
    ActiveCell.Offset(0, 9).Value = TextBox7.Text
    ActiveCell.Offset(0, 10).Value = TextBoX8.Text
    ActiveCell.Offset(0, 11).Value = TextBox9.Text
    ActiveCell.Offset(0, 12).Value = TextBox10.Text
    ActiveCell.Offset(0, 13).Value = TextBox11.Text
    
    If OptionButton1 = True Then
        ActiveCell.Offset(0, 14).Value = "Y"
    ElseIf OptionButton2 = True Then
        ActiveCell.Offset(0, 14).Value = "N"
    End If
    
    ActiveCell.Offset(0, 15).Value = TextBox12.Text
    ActiveCell.Offset(0, 16).Value = Sheet
    ActiveCell.Offset(0, 17).Value = ComboBox5.Text
    
    If OptionButton3 = True Then
        ActiveCell.Offset(0, 18).Value = "Y"
    Else
        ActiveCell.Offset(0, 18).Value = "N"
    End If
    
    
    'Sort Master Sheet
    
    ActiveWorkbook.Worksheets("MASTER SHEET").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("MASTER SHEET").Sort.SortFields.Add Key:=Range("A1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("MASTER SHEET").Sort
        .SetRange Range("A2:S90")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With


    
    'Update Address Master Sheet
    
     If Sheet = "ADDRESS MASTER SHEET" Then
        MsgBox "Select Class", vbInformation, "Error"
    Exit Sub
    End If
    
    Sheets("ADDRESS MASTER SHEET").Select
    Set findBlank = Range("A1:A72").Find(What:="", lookat:=xlWhole)
        findBlank.Select
    
    ActiveCell.Value = TextBox1.Text
    ActiveCell.Offset(0, 1).Value = TextBox2.Text
    ActiveCell.Offset(0, 2).Value = TextBox5.Text
    ActiveCell.Offset(0, 3).Value = ComboBox3.Text
    ActiveCell.Offset(0, 4).Value = TextBoX8.Text
    ActiveCell.Offset(0, 5).Value = ComboBox5.Text
    ActiveCell.Offset(0, 6).Value = Sheet
    
    'Sort Address Master Sheet
    
    ActiveWorkbook.Worksheets("ADDRESS MASTER SHEET").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("ADDRESS MASTER SHEET").Sort.SortFields.Add Key:=Range( _
        "C2:C100"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("ADDRESS MASTER SHEET").Sort
        .SetRange Range("A1:G72")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    
    'Clear Form
    ComboBox1.Text = ""
    TextBox1.Value = ""
    TextBox2.Value = ""
    ComboBox2.Value = ""
    TextBox3.Value = ""
    TextBox4.Value = ""
    TextBox5.Value = ""
    ComboBox3.Value = ""
    ComboBox4.Value = ""
    TextBox7.Value = ""
    TextBoX8.Value = ""
    TextBox9.Value = ""
    TextBox10.Value = ""
    TextBox11.Value = ""
    OptionButton1.Value = ""
    OptionButton2.Value = ""
    OptionButton3.Value = ""
    OptionButton4.Value = ""
    TextBox12.Value = ""
    ComboBox5.Value = ""
    Exit Sub
1:     MsgBox "Error: Sheet Full"
End Sub
  


Private Sub CommandButton2_Click()
Unload UserForm1
End Sub


Private Sub UserForm_Initialize()
With ComboBox1
.AddItem "PREK-K"
.AddItem "1ST-2ND"
.AddItem "3RD-4TH"
.AddItem "5TH-6TH BOYS"
.AddItem "5TH-6TH GIRLS"


With ComboBox2
.AddItem "PREK"
.AddItem "K"
.AddItem "1ST"
.AddItem "2ND"
.AddItem "3RD"
.AddItem "4TH"
.AddItem "5TH"
.AddItem "6TH"


With ComboBox3
.AddItem "Piedmont"
.AddItem "Patterson"


With ComboBox4
.AddItem "63957"
.AddItem "63956"


With ComboBox5
.AddItem "1"
.AddItem "2"
.AddItem "3"


End With
End With
End With
End With
End With
End Sub
 
Last edited by a moderator:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,759
Office Version
2007
Platform
Windows
I didn't find any problem, however I made some adjustments to your code.
Try:

Code:
Private Sub CommandButton1_Click()
[COLOR=#0000ff]  Dim lr As Long[/COLOR]
  Application.ScreenUpdating = False
  Sheet = ComboBox1.Text
  If Sheet = "" Then
    MsgBox "Select Class", vbInformation, "Error"
    Exit Sub
  End If
  'Update each class sheet Code
  Sheets(Sheet).Select
[COLOR=#0000ff]  Set findBlank = Range("A:A").Find(What:="", lookat:=xlWhole)[/COLOR]
  findBlank.Select
  ActiveCell.Value = TextBox1.Text
  ActiveCell.Offset(0, 1).Value = TextBox2.Text
  ActiveCell.Offset(0, 2).Value = ComboBox2.Text
  ActiveCell.Offset(0, 3).Value = TextBox3.Text
  ActiveCell.Offset(0, 4).Value = TextBox4.Text
  ActiveCell.Offset(0, 5).Value = TextBox5.Text
  ActiveCell.Offset(0, 6).Value = ComboBox3.Text
  ActiveCell.Offset(0, 7).Value = TextBox6.Text
  ActiveCell.Offset(0, 8).Value = ComboBox4.Text
  ActiveCell.Offset(0, 9).Value = TextBox7.Text
  ActiveCell.Offset(0, 10).Value = TextBox8.Text
  ActiveCell.Offset(0, 11).Value = TextBox9.Text
  ActiveCell.Offset(0, 12).Value = TextBox10.Text
  ActiveCell.Offset(0, 13).Value = TextBox11.Text
  If OptionButton1 = True Then
    ActiveCell.Offset(0, 14).Value = "Y"
  ElseIf OptionButton2 = True Then
    ActiveCell.Offset(0, 14).Value = "N"
  End If
  ActiveCell.Offset(0, 15).Value = TextBox12.Text
  
  'Update Master Sheet
  If Sheet = "MASTER SHEET" Then
    MsgBox "Select Class", vbInformation, "Error"
    Exit Sub
  End If
  
  Sheets("MASTER SHEET").Select
[COLOR=#0000ff]  Set findBlank = Range("A:A").Find(What:="", lookat:=xlWhole)[/COLOR]
[COLOR=#0000ff]  lr = findBlank.Row + 1[/COLOR]
  findBlank.Select
  ActiveCell.Value = TextBox1.Text
  ActiveCell.Offset(0, 1).Value = TextBox2.Text
  ActiveCell.Offset(0, 2).Value = ComboBox2.Text
  ActiveCell.Offset(0, 3).Value = TextBox3.Text
  ActiveCell.Offset(0, 4).Value = TextBox4.Text
  ActiveCell.Offset(0, 5).Value = TextBox5.Text
  ActiveCell.Offset(0, 6).Value = ComboBox3.Text
  ActiveCell.Offset(0, 7).Value = TextBox6.Text
  ActiveCell.Offset(0, 8).Value = ComboBox4.Text
  ActiveCell.Offset(0, 9).Value = TextBox7.Text
  ActiveCell.Offset(0, 10).Value = TextBox8.Text
  ActiveCell.Offset(0, 11).Value = TextBox9.Text
  ActiveCell.Offset(0, 12).Value = TextBox10.Text
  ActiveCell.Offset(0, 13).Value = TextBox11.Text
  If OptionButton1 = True Then
    ActiveCell.Offset(0, 14).Value = "Y"
  ElseIf OptionButton2 = True Then
    ActiveCell.Offset(0, 14).Value = "N"
  End If
  ActiveCell.Offset(0, 15).Value = TextBox12.Text
  ActiveCell.Offset(0, 16).Value = Sheet
  ActiveCell.Offset(0, 17).Value = ComboBox5.Text
  If OptionButton3 = True Then
    ActiveCell.Offset(0, 18).Value = "Y"
  Else
    ActiveCell.Offset(0, 18).Value = "N"
  End If
  'Sort Master Sheet
  ActiveWorkbook.Worksheets("MASTER SHEET").Sort.SortFields.Clear
  ActiveWorkbook.Worksheets("MASTER SHEET").Sort.SortFields.Add Key:=Range("A1"), _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers
  With ActiveWorkbook.Worksheets("MASTER SHEET").Sort
[COLOR=#0000ff]    .SetRange Range("A2:S" & lr)[/COLOR]
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
  End With
  'Update Address Master Sheet
  If Sheet = "ADDRESS MASTER SHEET" Then
    MsgBox "Select Class", vbInformation, "Error"
    Exit Sub
  End If
  Sheets("ADDRESS MASTER SHEET").Select
[COLOR=#0000ff]  Set findBlank = Range("A:A").Find(What:="", lookat:=xlWhole)[/COLOR]
[COLOR=#0000ff]  lr = findBlank.Row + 1[/COLOR]
  findBlank.Select
  ActiveCell.Value = TextBox1.Text
  ActiveCell.Offset(0, 1).Value = TextBox2.Text
  ActiveCell.Offset(0, 2).Value = TextBox5.Text
  ActiveCell.Offset(0, 3).Value = ComboBox3.Text
  ActiveCell.Offset(0, 4).Value = TextBox8.Text
  ActiveCell.Offset(0, 5).Value = ComboBox5.Text
  ActiveCell.Offset(0, 6).Value = Sheet
  'Sort Address Master Sheet
  ActiveWorkbook.Worksheets("ADDRESS MASTER SHEET").Sort.SortFields.Clear
  ActiveWorkbook.Worksheets("ADDRESS MASTER SHEET").Sort.SortFields.Add _
    [COLOR=#0000ff]Key:=Range("C2:C" & lr)[/COLOR], SortOn:=xlSortOnValues, Order:=xlAscending, _
    DataOption:=xlSortNormal
  With ActiveWorkbook.Worksheets("ADDRESS MASTER SHEET").Sort
[COLOR=#0000ff]    .SetRange Range("A1:G" & lr)[/COLOR]
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
  End With
  'Clear Form
  ComboBox1.Text = ""
  TextBox1.Value = ""
  TextBox2.Value = ""
  ComboBox2.Value = ""
  TextBox3.Value = ""
  TextBox4.Value = ""
  TextBox5.Value = ""
  ComboBox3.Value = ""
  ComboBox4.Value = ""
  TextBox7.Value = ""
  TextBox8.Value = ""
  TextBox9.Value = ""
  TextBox10.Value = ""
  TextBox11.Value = ""
  OptionButton1.Value = ""
  OptionButton2.Value = ""
  OptionButton3.Value = ""
  OptionButton4.Value = ""
  TextBox12.Value = ""
  ComboBox5.Value = ""
End Sub
  
Private Sub CommandButton2_Click()
  Unload UserForm1
End Sub
  
Private Sub UserForm_Initialize()
  With ComboBox1
    .AddItem "PREK-K"
    .AddItem "1ST-2ND"
    .AddItem "3RD-4TH"
    .AddItem "5TH-6TH BOYS"
    .AddItem "5TH-6TH GIRLS"
  End With
  With ComboBox2
    .AddItem "PREK"
    .AddItem "K"
    .AddItem "1ST"
    .AddItem "2ND"
    .AddItem "3RD"
    .AddItem "4TH"
    .AddItem "5TH"
    .AddItem "6TH"
  End With
  With ComboBox3
    .AddItem "Piedmont"
    .AddItem "Patterson"
  End With
  With ComboBox4
    .AddItem "63957"
    .AddItem "63956"
  End With
  With ComboBox5
    .AddItem "1"
    .AddItem "2"
    .AddItem "3"
  End With
End Sub
 

PMRetired2012

New Member
Joined
Aug 6, 2019
Messages
27
what i want to do is.
i want a foumula that will let me to get a rolling average for a month as i add numbers for each week. that total will be in one cell. I want it to be able for it to figure a average for week 1,2,3,4 so that I will have a current average each week.
Example:

week 1 attendance 20
week 2 atten 45 20 + 45= 65...65/2= 33 adverage so far for month
week 3 attendence 25 20+45+75=..140/3=46 adverage so far for month
week 4 attendence 95 20+45+25+95=180/4=180/4 = 46.25 adverage for the whole month.
So another words i need a formula that keeps a running total for eaach week till we get to week 4 and that represents the total monthly total for that month

Thanks
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,759
Office Version
2007
Platform
Windows
did you try the modified code? What you mention is a new requirement, I am glad to review it but you must create a new thread. I suggest you include examples in your explanation.
 

PMRetired2012

New Member
Joined
Aug 6, 2019
Messages
27
Not yet on code..i just started a new thread..if you want to you can answer the question DanteAmor.

thanks
 

Forum statistics

Threads
1,077,851
Messages
5,336,750
Members
399,100
Latest member
darcob

Some videos you may like

This Week's Hot Topics

Top