After transfering data from a userform one sheet becomes hightlighted

P

PMRetired2012

Board Regular
Joined
Aug 6, 2019
Messages
123
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
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Sort by date Sort by votes
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:
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
I was try to load data To List Box And Transfer data to Sheet2.... (Not work this userform)
Replies
4
Views
181
krunal123
K
P
  • Solved
Transferring data between two userforms problem .
Replies
7
Views
512
Pacman52
P
P
  • Question Question
How to VBA code to drop data from UserForm to multiple worksheets
2
Replies
17
Views
596
dmt32
D
J
  • Question Question
Excel VBA Print Name Tag from Userform
Replies
2
Views
334
Rhodie72
Rhodie72
P
  • Question Question
VBA issue. runtime 91 - only occurring after change to Range?
Replies
6
Views
428
PTEOMARK
P

Forum statistics

Threads
1,223,702
Messages
6,173,969
Members
452,539
Latest member
delvey

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top