cannot get focus to direct to a specific worksheet uploading userform

K

kbishop94

Active Member
Joined
Dec 5, 2016
Messages
476
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
* thats supposed to be 'after UNLOADING userform'... (not 'uploading') sorry, my bad.

when the form is opened, it asks for a date range. when a commandbutton is selected, it gets the data from a worksheet ("Seatex Incident Log") and then copies it and pastes it onto a different worksheet ("REPORTS"), but I cant get it to stay on the worksheet REPORTS when the form closes... If I remove the 'unload' command from the form, then it works (of course the form is still on the screen covering up most of the worksheet.)
I used this same technique and code on another workbook, but it is executed from a commandbutton on a worksheet and not a userform like this one is doing. What am I missing?? Here is the code:

This is the code for the command button that executes the action of filtering the data from the main worksheet ("Seatex Incident Log") , copying it, and then pasting it onto another worksheet ("REPORTS)... which is the worksheet that I need the focus to be set to.

I ve already tried moving around this part of the code
Code: 
ActiveWorkbook.Worksheets("REPORTS").Activate
ActiveWorkbook.Worksheets("REPORTS").Visible = True
ActiveWorkbook.Worksheets("REPORTS").Range("A4").Select

to different places (before or after 'unload me') and it doesnt matter what where i put or what i try... after the form unloads no matter what it always going back to the "Seatex Incident Log" worksheet.


Code: 
[COLOR=#008000]' *****************************************************[/COLOR][COLOR=#008000]****************************************************[/COLOR][COLOR=#008000]
' CODE FOR SELECTING JUST THE RECORDS ON THE WORKSHEET "SEATEX INCIDENT LOG" THAT FALL WITHIN THE DATE RANGE THAT WAS PREVISOULY SELECTED ON THE USERFORM[/COLOR]

Private Sub cmdGetReport_Click()

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

Dim rCol As Long
Dim ws As Worksheet
Dim W1Startdate As String, W1Enddate As String

[COLOR=#008000]'set object variable to worksheet[/COLOR]
    Set ws = ThisWorkbook.Worksheets("Seatex Incident Log")

W1Startdate = Me.txtDateBox2.Value     [COLOR=#008000] ' "FROM" date[/COLOR]
W1Enddate = Me.txtDateBox3.Value        [COLOR=#008000]' "TO" date[/COLOR]

rCol = Sheets("Seatex Incident Log").UsedRange.Rows.Count
    
     With ws
ActiveWorkbook.Worksheets("Seatex Incident Log").Activate
        .Sort.SortFields.Clear

        With .Range(Cells(17, 1), Cells(rCol, 29))
[COLOR=#008000]'clear filter[/COLOR]
        .AutoFilter
        .AutoFilter Field:=2, _
                    Criteria1:=">=" & CLng(DateValue(W1Startdate)), _
                    Criteria2:="<=" & CLng(DateValue(W1Enddate))
        End With
    End With

[COLOR=#008000]' ****************************************************[/COLOR][COLOR=#008000]****************************************************[/COLOR][COLOR=#008000]*[/COLOR][COLOR=#008000]
[/COLOR][COLOR=#008000]' ****************************************************[/COLOR][COLOR=#008000]****************************************************[/COLOR][COLOR=#008000]*[/COLOR][COLOR=#008000]
' CODE FOR COPYING ALL THE VISIBLE ROWS THAT HAVE BEEN FILTERED DOWN TO AFTER THE DATE QUERY AND THE INCIDENT TYPE QUERY HAS BEEN RAN AND PASTING_
    'ALL THAT TO THE HIDDEN SHEET "REPORTS"
[/COLOR]
ActiveWorkbook.Worksheets("REPORTS").Activate
ActiveSheet.Range("A4").EntireRow.Select
ActiveSheet.Range(Selection, Selection.End(xlDown)).Select
Selection.Clear

[COLOR=#008000]' ****************************************************[/COLOR][COLOR=#008000]****************************************************[/COLOR][COLOR=#008000]*[/COLOR]
ActiveWorkbook.Worksheets("Seatex Incident Log").Activate
ActiveWorkbook.Worksheets("Seatex Incident Log").Range(Cells(18, 1), Cells(rCol, 29)).SpecialCells(xlCellTypeVisible).Select
Selection.Copy

[COLOR=#008000]' ****************************************************[/COLOR][COLOR=#008000]****************************************************[/COLOR][COLOR=#008000]*[/COLOR]
ActiveWorkbook.Worksheets("REPORTS").Activate
ActiveWorkbook.Worksheets("REPORTS").Range("A4").Select

Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    
    Application.CutCopyMode = False

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

Unload Me

ActiveWorkbook.Worksheets("REPORTS").Activate
ActiveWorkbook.Worksheets("REPORTS").Visible = True
ActiveWorkbook.Worksheets("REPORTS").Range("A4").Select

End Sub
 
Last edited:

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Sort by date Sort by votes
When you unload the userform, all subsequent coe in the userform is not executed .

Try activating the worksheet before unloading the userform .
 
Upvote 0
Jaafar Tribak said:
When you unload the userform, all subsequent coe in the userform is not executed .

Try activating the worksheet before unloading the userform .
Click to expand...

Thank you for your suggestion, Jaafar.

I did try that, putting the:

Code: 
ActiveWorkbook.Worksheets("REPORTS").Activate
ActiveWorkbook.Worksheets("REPORTS").Visible = True
ActiveWorkbook.Worksheets("REPORTS").Range("A4").Select

before the 'unload me'. Makes no difference, it still reverts back to the "Seatex Incident Log" sheet.

However, if I comment out the 'unload me' command, then it does stay on the "REPORTS" sheet. So for whatever reason that I cant understand, after it executes the 'unload me' it just goes straight back to the "Seatex Incident Log" worksheet.
 
Upvote 0
Declare a boolean flag varaible at the top of the userform module as follows:
Code: 
Private bFlag As Boolean

Then amend your code as follows (changes in blue):
Code: 
Private Sub cmdGetReport_Click()

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

Dim rCol As Long
Dim ws As Worksheet
Dim W1Startdate As String, W1Enddate As String

'set object variable to worksheet
    Set ws = ThisWorkbook.Worksheets("Seatex Incident Log")


W1Startdate = Me.txtDateBox2.Value      ' "FROM" date
W1Enddate = Me.txtDateBox3.Value        ' "TO" date


rCol = Sheets("Seatex Incident Log").UsedRange.Rows.Count
    
     With ws
ActiveWorkbook.Worksheets("Seatex Incident Log").Activate
        .Sort.SortFields.Clear


        With .Range(Cells(17, 1), Cells(rCol, 29))
'clear filter
        .AutoFilter
        .AutoFilter Field:=2, _
                    Criteria1:=">=" & CLng(DateValue(W1Startdate)), _
                    Criteria2:="<=" & CLng(DateValue(W1Enddate))
        End With
    End With

' *********************************************************************************************************
' *********************************************************************************************************
' CODE FOR COPYING ALL THE VISIBLE ROWS THAT HAVE BEEN FILTERED DOWN TO AFTER THE DATE QUERY AND THE INCIDENT TYPE QUERY HAS BEEN RAN AND PASTING_
    'ALL THAT TO THE HIDDEN SHEET "REPORTS"


ActiveWorkbook.Worksheets("REPORTS").Activate
ActiveSheet.Range("A4").EntireRow.Select
ActiveSheet.Range(Selection, Selection.End(xlDown)).Select
Selection.Clear


' *********************************************************************************************************
ActiveWorkbook.Worksheets("Seatex Incident Log").Activate
ActiveWorkbook.Worksheets("Seatex Incident Log").Range(Cells(18, 1), Cells(rCol, 29)).SpecialCells(xlCellTypeVisible).Select
Selection.Copy


' *********************************************************************************************************
ActiveWorkbook.Worksheets("REPORTS").Activate
ActiveWorkbook.Worksheets("REPORTS").Range("A4").Select


Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    
    Application.CutCopyMode = False


Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

[COLOR=#0000ff][B]bFlag = True[/B][/COLOR]
Unload Me

End Sub


[COLOR=#0000ff][B]Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

    If bFlag Then
        ActiveWorkbook.Worksheets("REPORTS").Activate
        ActiveWorkbook.Worksheets("REPORTS").Visible = True
        ActiveWorkbook.Worksheets("REPORTS").Range("A4").Select
    End If
End Sub[/B][/COLOR]
 
Last edited:
Upvote 0
Jaafar Tribak said:
Declare a boolean flag varaible at the top of the userform module as follows:
Code: 
Private bFlag As Boolean

Then amend your code as follows (changes in blue):
Code: 
Private Sub cmdGetReport_Click()

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

Dim rCol As Long
Dim ws As Worksheet
Dim W1Startdate As String, W1Enddate As String

'set object variable to worksheet
    Set ws = ThisWorkbook.Worksheets("Seatex Incident Log")


W1Startdate = Me.txtDateBox2.Value      ' "FROM" date
W1Enddate = Me.txtDateBox3.Value        ' "TO" date


rCol = Sheets("Seatex Incident Log").UsedRange.Rows.Count
    
     With ws
ActiveWorkbook.Worksheets("Seatex Incident Log").Activate
        .Sort.SortFields.Clear


        With .Range(Cells(17, 1), Cells(rCol, 29))
'clear filter
        .AutoFilter
        .AutoFilter Field:=2, _
                    Criteria1:=">=" & CLng(DateValue(W1Startdate)), _
                    Criteria2:="<=" & CLng(DateValue(W1Enddate))
        End With
    End With

' *********************************************************************************************************
' *********************************************************************************************************
' CODE FOR COPYING ALL THE VISIBLE ROWS THAT HAVE BEEN FILTERED DOWN TO AFTER THE DATE QUERY AND THE INCIDENT TYPE QUERY HAS BEEN RAN AND PASTING_
    'ALL THAT TO THE HIDDEN SHEET "REPORTS"


ActiveWorkbook.Worksheets("REPORTS").Activate
ActiveSheet.Range("A4").EntireRow.Select
ActiveSheet.Range(Selection, Selection.End(xlDown)).Select
Selection.Clear


' *********************************************************************************************************
ActiveWorkbook.Worksheets("Seatex Incident Log").Activate
ActiveWorkbook.Worksheets("Seatex Incident Log").Range(Cells(18, 1), Cells(rCol, 29)).SpecialCells(xlCellTypeVisible).Select
Selection.Copy


' *********************************************************************************************************
ActiveWorkbook.Worksheets("REPORTS").Activate
ActiveWorkbook.Worksheets("REPORTS").Range("A4").Select


Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    
    Application.CutCopyMode = False


Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

[COLOR=#0000ff][B]bFlag = True[/B][/COLOR]
Unload Me

End Sub


[COLOR=#0000ff][B]Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

    If bFlag Then
        ActiveWorkbook.Worksheets("REPORTS").Activate
        ActiveWorkbook.Worksheets("REPORTS").Visible = True
        ActiveWorkbook.Worksheets("REPORTS").Range("A4").Select
    End If
End Sub[/B][/COLOR]
Click to expand...

Added it just like you have prepared for me (thank you, btw) but it still directs it to the main (Seatex Incident Log) worksheet. Any other suggestions?
 
Upvote 0
Do you have any other code elswhere that might "Seatex Incident Log" sheet ?
 
Upvote 0
Jaafar Tribak said:
Do you have any other code elswhere that might "Seatex Incident Log" sheet ?
Click to expand...

I changed how the form is called upon (activated) from the action of clicking on a sheet tab ("Get Report") to a command button that I put on the worksheet itself ("Seatex Incident Log".) After I did this it worked(!)
Then I saw your post this morning and this confirmed to me that there just has to be something else I was missing... and then I found it. Here is the code for the Sheet tab "Get Report" :

Code: 
Private Sub Worksheet_Activate()

frmReportCriteria.Show
[COLOR=#ff0000][B]Worksheets("Seatex Incident Log").Activate[/B][/COLOR]

End Sub

I honestly do not remember putting the activate worksheet in there at all, but that was it. Once i removed it, it worked just as intended. Regardless, thank you so much for helping me with this.
icon14.png
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
add a Find/Update button to my userform1 that will allow me to find a report # in my worksheets, repopulate the userform.
Replies
7
Views
383
dmt32
D
K
  • Question Question
cant get code to jump down to my onerror script when encountering this error...
Replies
8
Views
460
Candyman8019
C
K
  • Question Question
help with VBA to find specific text within a string and within a specific range
Replies
1
Views
2K
kbishop94
K
I
  • Question Question
Worksheet value is updated only after userform is closed
2
Replies
13
Views
482
ipbr21054
I
D
  • Question Question
VBA code works when called directly, but not from another routhine
Replies
7
Views
942
davidepstein22
D

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

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