Return to Previous Sheet

[Hazel]

Hi All

Had some fantastic help from Tom yesterday and it worked perfectly no
problem - however today when I added to the "Result Sheet" I had to close
down the UserForm and start again with the ActiveSheet I was using to search
again for more info.
Below is the code from yesterday followed by Adding the info found to the
"Result Sheet" how do I get back to my previous Active Sheet.

Private Sub Cmd100_Click()
Dim rngToSearch As Range
Dim rngFound As Range
Set rngToSearch = ActiveSheet.Columns("F")
Set rngFound = rngToSearch.Find(What:=Tb100.Value, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
MatchCase:=False)
If rngFound Is Nothing Then
MsgBox "Sorry " & Tb100.Value & " was not found."
Else
With Lb1
For i = 0 To .ListCount - 1
If .List(i, 5) = Tb100.Value Then
Lb1.ListIndex = i
For Each cell In Range(Cells(rngFound.Row, 7), _
Cells(rngFound.Row, 7))
s = cell.Value & ","
Next
Tb101.Value = Left(s, Len(s) - 1)
Exit For
End If
Next
rngFound.Select
End With
End If


End Sub

Private Sub Cmd2_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Result")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 5) _
.End(xlUp).Offset(1, 0).Row



'copy the data to the database
ws.Cells(iRow, 2).Value = Me.Tb25.Value
ws.Cells(iRow, 3).Value = Me.Tb17.Value
ws.Cells(iRow, 4).Value = Me.Tb18.Value
ws.Cells(iRow, 5).Value = Me.Tb14.Value
ws.Cells(iRow, 6).Value = Me.Tb15.Value
ws.Cells(iRow, 7).Value = Me.Tb16.Value
ws.Cells(iRow, 8).Value = Lb1.List(, 4)
ws.Cells(iRow, 9).Value = Me.Tb2.Text
ws.Cells(iRow, 10).Value = Me.Tb19.Value

'Call Module1.Macro4


'clear the data
Me.Tb2.Text = ""
Me.Tb11.Value = ""
Me.Tb12.Value = ""
Me.Tb13.Value = ""
Me.Tb14.Value = ""
Me.Tb15.Value = ""
Me.Tb16.Value = ""
Me.Tb19.Value = ""
Me.Tb26.Value = ""
Me.Tb27.Value = ""
Me.Tb100.Value = ""



End Sub

 

[JW]

Don't exactly follow what you are asking. If you just want to return
to the original sheet that you started the macro on, you can set it to
a variable at the top of your code and then just activate that sheet
whenever you need to within your code. Something like:
Dim startSheet as Worksheet
Set startSheet = ActiveSheet

Then use something like below to return to the startSheet
startSheet.Activate

Be sure to set the variable to nothing at the end of the sub
startSheet = Nothing

Or, you could use the sheet name instead
Dim startSheet a s String
startSheet = ActiveSheet.Name

Then use something like below to return to the startSheet
Worksheets(startSheet).Activate

HTH

 

[Hazel]

Hi Jw

Perhaps should have explained that there are 26 sheets in the Workbook each
one named and selected by a drop down Combo Box that is why I used
ActiveSheet. Therefore if I select the " J.Bloggs " sheet and add the info
into the "Result" sheet I then want the macro after inserting the info in the
"Result" Sheet to return to the "J.Bloggs" Sheet which was the original
ActiveSheet.

Hope that explains it better what I want to do.
--
Many thanks

hazel

Don't exactly follow what you are asking. If you just want to return
to the original sheet that you started the macro on, you can set it to
a variable at the top of your code and then just activate that sheet
whenever you need to within your code. Something like:
Dim startSheet as Worksheet
Set startSheet = ActiveSheet

Then use something like below to return to the startSheet
startSheet.Activate

Be sure to set the variable to nothing at the end of the sub
startSheet = Nothing

Or, you could use the sheet name instead
Dim startSheet a s String
startSheet = ActiveSheet.Name

Then use something like below to return to the startSheet
Worksheets(startSheet).Activate

HTH

 

[Hazel]

Hi JW

Sorry jumped the gun there !!! I read the post and answered without even
attempting to try your suggestions -- since tried below and does it perfectly
thanks again for your help.


Dim startSheet as Worksheet

Set startSheet = ActiveSheet

Then use something like below to return to the startSheet
startSheet.Activate

--
Many thanks

hazel

Don't exactly follow what you are asking. If you just want to return
to the original sheet that you started the macro on, you can set it to
a variable at the top of your code and then just activate that sheet
whenever you need to within your code. Something like:
Dim startSheet as Worksheet
Set startSheet = ActiveSheet

Then use something like below to return to the startSheet
startSheet.Activate

Be sure to set the variable to nothing at the end of the sub
startSheet = Nothing

Or, you could use the sheet name instead
Dim startSheet a s String
startSheet = ActiveSheet.Name

Then use something like below to return to the startSheet
Worksheets(startSheet).Activate

HTH