Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return to Previous Sheet
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 -- Many thanks hazel |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return to Previous Sheet
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 wrote: 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 -- Many thanks hazel |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return to Previous Sheet
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 "JW" wrote: 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 wrote: 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 -- Many thanks hazel |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return to Previous Sheet
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 "JW" wrote: 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 wrote: 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 -- Many thanks hazel |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to return to previous cell? | Excel Discussion (Misc queries) | |||
How to return to the previous active sheet after hyperlink? | Excel Worksheet Functions | |||
LOOKUP return the value from the previous row | Excel Worksheet Functions | |||
Hyperlink or Other Method To Return To Previous Location (Sheet) Possible? | New Users to Excel | |||
Return to previous worksheet | Excel Programming |