Focus back to the Excel Sheet I started on after the Database Updates on a sheet.

[ca1358]

I created a Test file. It includes the Excel Sheet and the Northwinds Database.

I start on page “GN15”, click the CommandButton1. In cell (“T11”) it puts in the Sheet Name. Then opens up UserForm1. Update the Sheet “MRP” from the Northwinds Database and Update UserForm Textbox1 With the Sheet Name that we started on.

What I need to do from here have the Focus back to the Sheet I start on, in this case Sheet(“GN15”). I thought by putting the name of the sheet I start on and putting it on the UserForm I could tell it to goback to that sheet I started on.

But now I stumped. Any help would greatly be appreciated.

[jaslake]

Hi ca1358

You probably don't need to do this with a UserForm but, since that's what you have, I've gone with that. I'm only attaching the "Test" file as that's where the code resides. I've modified this code

Please Login or Register  to view this content.

I've added a "Cancel" button to the UserForm and this code is behind that button

Please Login or Register  to view this content.

If you stay with the UserForm you'll probably want do disable the "X" on the UserForm" so the user MUST use the "Cancel" button to close the form...otherwise the procedure won't run. There's plenty of code available to help you do so but, if you need help with this, let me know.

To test the procedure I did change the Path of the Access file...I believe I got them all changed back in the attached. If not the procedure will tell you soon enough.

Let me know of issues.

[ca1358]

Thank you for you time.

Is there anyway to keep the Userform open and go back to Sheets I start on?

[jaslake]

Hi ca1358

Sure. Simply comment out or delete this line of code

Please Login or Register  to view this content.

in this code

Please Login or Register  to view this content.

[ca1358]

When I click on the cancel button getting runtime error 9 Subscript out of range.

[jaslake]

Hi ca1358

I'd like you to post the file (test only) where you get the error. I'm unable to duplicate it.

[ca1358]

I click on the Cancel button and get the error on this line

Sheets(UserForm1.TextBox1.Value).Activate

=====================================

Private Sub CommandButton1_Click()
Sheets(UserForm1.TextBox1.Value).Activate
' Unload Me
End Sub

[jaslake]

Hi ca1358

As I said

I'd like you to post the file (test only) where you get the error. I'm unable to duplicate it.

Unless I can work with what you're working with I'm unable to troubleshoot it. Please post the file.

[ca1358]

I apologize for lag in time but I am balancing the nightly process.. I include the TEST file.

[jaslake]

Hi ca1358

Look at the code in Sheet GN15 for the Command Button...you'll see, as I previously described, I changed it. You still have the old code in your most recent attachment. Change the code as follows:

My code

Please Login or Register  to view this content.

Your code

Please Login or Register  to view this content.

Let me know of issues.

[ca1358]

I changed the code provided and changed this part also and works perfectly.

Many thanks!!

Please Login or Register  to view this content.