Return to original worksheet

[rickblunt]

Hello, I have a WB with multiple WS's and on these sheets are a variety of userforms. Some sheets actually have more than one userform on their sheets, depending on what the sheet is for. Everything works fine, all of the userforms play together well so that is fine. The users have noted that they would like to send me a quick email right from the userforms without breaking out of the WB. So I whipped another userform with a couple of text boxes in it to allow the users to add in comments and their names and such, this works fine as well. The only thing that they do not like is that it moves them out of the WS that they are in when they access the comment userform. So then they have to navigate back to it.

What has got me stumped is that if I want to add a "comment" button to all of the userforms, how would I tweak the following codes so that after the email is sent you are returned to whatever worksheet you were already on; instead of the suggestion log? Basically they want to stay on the same sheet that they are working on when they call and then close out the suggestion userform. Can anyone point me in a direction to take on trying to do this? I appreciate any input.

This is my Comments code:

Sub SendButton_Click()


With Sheets("Suggestions")


nextrow = .Range("A" & Rows.Count).End(xlUp).Row + 1

.Range("A" & nextrow) = Now()
.Range("B" & nextrow) = CommentBox.Value
.Range("C" & nextrow) = NameBox.Value

Worksheets("Suggestions").Activate

ActiveSheet.Range("F1:H2").Select

ActiveWorkbook.EnvelopeVisible = True

With ActiveSheet.MailEnvelope
.Introduction = " The following comment or suggestion for the Inspection App is being submitted. "
.Item.To = "username@email.com;"
.Item.Subject = "Inspection App Comment or Suggestion"
.Item.Send
End With
End With

    CommentBox.Value = ""
    NameBox.Value = ""

    Unload SuggestionEmail


End Sub

This is an example of the code that I am using to call the comment userform, I have this same code populating userforms on 9 sheets:

Private Sub SendComment_Click()

    SuggestionEmail.Show


End Sub

 

[offthelip]

I have implemented a "Back" system , which allowed a user to return to the previous worksheet from anywhere in the entire workbook.
I used two "named ranges" ( just because they are saved with the workbook and continue to work after closing and reopening the workbook)
This could easily be adapted to do what you want.

Private Sub CommandButton4_Click() ' this is the back button on every sheet
Call Goback
End Sub

Sub Goback()         ' this is module code
Dim str As String
Dim Lent As Integer

 str = (ActiveWorkbook.Names("prevsheet"))
 Lent = Len(str)
 str = Mid(str, 3, Lent - 3)
Worksheets(str).Activate

End Sub


Private Sub Worksheet_Activate()  ' this is in every worksheet activate 

    ActiveWorkbook.Names.Add Name:="prevsheet", RefersTo:=ActiveWorkbook.Names("currsheet")
    ActiveWorkbook.Names.Add Name:="currsheet", RefersTo:=ActiveSheet.Name

 

[Fluff]

Another option

Sub SendButton_Click()
   Dim Ws As Worksheet
   Set Ws = ActiveSheet
   
   With Sheets("Suggestions")
   
      
      nextrow = .Range("A" & Rows.Count).End(xlUp).Row + 1
      
      .Range("A" & nextrow) = Now()
      .Range("B" & nextrow) = CommentBox.Value
      .Range("C" & nextrow) = NameBox.Value
      
      Worksheets("Suggestions").Activate
      
      ActiveSheet.Range("F1:H2").Select
      
      ActiveWorkbook.EnvelopeVisible = True
      
      With ActiveSheet.MailEnvelope
         .Introduction = " The following comment or suggestion for the Inspection App is being submitted. "
         .Item.To = "username@email.com;"
         .Item.Subject = "Inspection App Comment or Suggestion"
         .Item.Send
      End With
   End With

    CommentBox.Value = ""
    NameBox.Value = ""
    Ws.Activate
    Unload SuggestionEmail


End Sub

 

[rickblunt]

Another option

Sub SendButton_Click()
   Dim Ws As Worksheet
   Set Ws = ActiveSheet
   
   With Sheets("Suggestions")
  
     
      nextrow = .Range("A" & Rows.Count).End(xlUp).Row + 1
     
      .Range("A" & nextrow) = Now()
      .Range("B" & nextrow) = CommentBox.Value
      .Range("C" & nextrow) = NameBox.Value
     
      Worksheets("Suggestions").Activate
     
      ActiveSheet.Range("F1:H2").Select
     
      ActiveWorkbook.EnvelopeVisible = True
     
      With ActiveSheet.MailEnvelope
         .Introduction = " The following comment or suggestion for the Inspection App is being submitted. "
         .Item.To = "username@email.com;"
         .Item.Subject = "Inspection App Comment or Suggestion"
         .Item.Send
      End With
   End With

    CommentBox.Value = ""
    NameBox.Value = ""
    Ws.Activate
    Unload SuggestionEmail


End Sub

Hi Fluff, thanks for the suggestion, it worked great except for some of my userforms are modeless (I got the "can't show non-modal form when modal form is displayed" run time error, some of my forms are "calculators" so they have to stay visible, have to noodle on that one and start another thread if I have to, I appreciate the assistance.

 

[rickblunt]

Hi offthelip, i am working with your suggestion now (tried the easy one first ) it will take me a little longer to try out, I do appreciate the help.

 

[rickblunt]

I have implemented a "Back" system , which allowed a user to return to the previous worksheet from anywhere in the entire workbook.
I used two "named ranges" ( just because they are saved with the workbook and continue to work after closing and reopening the workbook)
This could easily be adapted to do what you want.

Private Sub CommandButton4_Click() ' this is the back button on every sheet
Call Goback
End Sub

Sub Goback()         ' this is module code
Dim str As String
Dim Lent As Integer

str = (ActiveWorkbook.Names("prevsheet"))
Lent = Len(str)
str = Mid(str, 3, Lent - 3)
Worksheets(str).Activate

End Sub


Private Sub Worksheet_Activate()  ' this is in every worksheet activate

    ActiveWorkbook.Names.Add Name:="prevsheet", RefersTo:=ActiveWorkbook.Names("currsheet")
    ActiveWorkbook.Names.Add Name:="currsheet", RefersTo:=ActiveSheet.Name

oops!

Hi offthelip, i am working with your suggestion now (tried the easy one first ) it will take me a little longer to try out, I do appreciate the help.​

 

[rickblunt]

Hi Fluff and offlthelip, thanks for your help. I actually used both of your idea to get this to work. I used fluff's code for the initial WS return, but then I used offthelips input to get past my modal errors. adding some code to load and unload the userforms in the workbook activate took care of everything. I sure appreciate the assistance.

 

[Fluff]

Glad you were able to sort it & thanks for the feedback