Switching worksheets unexpectedly after exiting UserForm

[GreenTurd]

Hello all,

This is my first post here and I have searched for this issue in the advanced search here and on the web. Finding it hard to describe it in a way that displays useful advice. I am certainly no expert at VBA, so I am hoping for a patient guide to ask a question that will lead me down the trail of figuring this out. I am just stuck not knowing what I don't know. So with that awesome introduction...

I have am creating a PO system of sorts in excel. I have buttons that calls forms and forms populating my spreadsheet with data. Then the user presses a "Finish PO" command button. At which time it exits the form. It is meant to exit the form back to the PO worksheet "tab" that it is called from, instead when it exits, it is jumping to a different worksheet "tab" and I can't figure out why. I realize that this could be happening from somewhere else in the code, but I am only seeing it happen immediately after I click the "FinishPOButton" to close the form. Hope this is enough for someone to guide me.

TIA
~Chris


'information regarding which row to start PO on
LineItemTotal = Range("LineItemTotal").Value
POrowstart = 19

'get product selection information from the "Product Selection" Userform
CurrentProduct = ItemList.Value

'Lookup related product information from the ProductListing Range
ProductID = Application.WorksheetFunction.VLookup(CurrentProduct, Range("ItemList"), 2, False)
UnitPrice = Application.WorksheetFunction.VLookup(CurrentProduct, Range("ItemList"), 3, False)
UOM = Application.WorksheetFunction.VLookup(CurrentProduct, Range("ItemList"), 4, False)

'Populate next line item with product selection
Range("D" & POrowstart + LineItemTotal).Value = CurrentProduct
Range("B" & POrowstart + LineItemTotal).Value = ProductID
Range("H" & POrowstart + LineItemTotal).Value = Quantity
Range("I" & POrowstart + LineItemTotal).Value = UOM
Range("J" & POrowstart + LineItemTotal).Value = UnitPrice

End Sub

Private Sub FinishPOButton_Click()
Unload Me

End Sub

[SamT]

It's obvious that the problem is in the 42nd line before
'information regarding which row to start PO on:devil2:

Please post all the code

[GreenTurd]

I didn't want to post that much code.


Private Sub ProductList_Click()
'This macro runs when an item in the list is clicked

Application.ScreenUpdating = True

'Declare Relevent Values
Dim CurrentProduct As String
Dim ProductID As String
Dim UnitPrice As Currency

'Grab Product Name from Product Listbox selection
CurrentProduct = ItemList.Value

'Lookup Product ID based on Product Description and change label
ProductID = Application.WorksheetFunction.VLookup(CurrentProduct, Range("ItemList"), 2, False)

'Lookup Unit Price based on Product Description and change label
UnitPrice = Application.WorksheetFunction.VLookup(CurrentProduct, Range("ItemList"), 3, False)

End Sub



Private Sub AddtoPO_Click()

'Declare Relevent Values
Dim CurrentProduct As String
Dim ProductID As String
Dim UnitPrice As Currency
Dim UOM As String
Dim Qty As Integer
Dim LineItemTotal As Integer
Dim POrowstart As Integer

'information regarding which row to start PO on
LineItemTotal = Range("LineItemTotal").Value
POrowstart = 19

'get product selection information from the "Product Selection" Userform
CurrentProduct = ItemList.Value

'Lookup related product information from the ProductListing Range
ProductID = Application.WorksheetFunction.VLookup(CurrentProduct, Range("ItemList"), 2, False)
UnitPrice = Application.WorksheetFunction.VLookup(CurrentProduct, Range("ItemList"), 3, False)
UOM = Application.WorksheetFunction.VLookup(CurrentProduct, Range("ItemList"), 4, False)

'Populate next line item with product selection
Range("D" & POrowstart + LineItemTotal).Value = CurrentProduct
Range("B" & POrowstart + LineItemTotal).Value = ProductID
Range("H" & POrowstart + LineItemTotal).Value = Quantity
Range("I" & POrowstart + LineItemTotal).Value = UOM
Range("J" & POrowstart + LineItemTotal).Value = UnitPrice

End Sub

Private Sub FinishPOButton_Click()
Unload Me

End Sub

[GreenTurd]

This is the code that leads up to the above macro being called. I don't think the error is here though because it doesn't switch workbooks until after the above macro has ended.


Private Sub ProductSelectButton_Click()
'This macro runs when Product Select Button is clicked

Application.ScreenUpdating = True

'Declare Relevent Values
Dim Vendor As String
Dim Originator As String

'Grab Info from Userform
Originator = OriginatorBox.Value
Vendor = VendorSelectBox.Value
ShipViaBit = ShipViaBox.Value
DueDate = DueDateTextBox.Text

'Error Message for Missing info
If Vendor = "" Or Originator = "" Then
MsgBox "Missing Required Information"

Else

'Lookup Vendor Information
VendorName = Application.WorksheetFunction.VLookup(Vendor, Range("Vendor"), 2, False)
VendorInfo = Application.WorksheetFunction.VLookup(Vendor, Range("Vendor"), 3, False)
VendorEmail = Application.WorksheetFunction.VLookup(Vendor, Range("Vendor"), 5, False)
VendorTerms = Application.WorksheetFunction.VLookup(Vendor, Range("Vendor"), 6, False)

'Hide PO generator userform and show product selection userform
POGenerator.Hide
ProductSelection.Show

'Insert current date as PO date and populate PO with selections
Range("PODate").Value = Date
Range("a" & "8").Value = VendorName
Range("a" & "9").Value = VendorInfo
Range("a" & "12").Value = VendorEmail
Range("originator").Value = Originator
Range("terms").Value = VendorTerms
Range("ShipVia").Value = ShipViaBit
Range("DueDate").Value = DueDate
End If

End Sub

[SamT]

I don't see any Worksheets declared anywhere in your code. That usually works, but it makes the Compiler guess what you really want.

Hint: I am lazy typist, so I usually

Dim Wsf As Object
Set WsF = Application.WorksheetFunction
Dim WSht1 As Worksheet
Set WSht1 = Sheets("Sheet1")

ProductID = WsF.VLookup(CurrentProduct, WSht1.Range("ItemList"), 2, False)

This should fix your issue

Private Sub FinishPOButton_Click()
Sheets("Sheet2").Activate
Unload Me

End Sub

Advanced Hint: A user changing a Tab Name can break your code. To prevent that, and to make ny code more trasnparent to any future code maintainer, I will Change important Sheet's CodeNames and use the CodeNames in my Code

Assuming Sheets("Sheet1") is a Product Data Sheet, Change the CodeName to something relevant, e.g ProData, then in Code

ProductID = WsF.VLookup(CurrentProduct, ProData.Range("ItemList"), 2, False)

You can still use shorter "names" in Code if you are lazy and have long CodeNames. Assume a CodeName of ElectronicProducts_ForUseInCommunications_DataBase

Dim EPC As Object
Set EPC = ElectronicProducts_ForUseInCommunications_DataBase

[GreenTurd]

Thank you so much for your help! :friends: Putting it there at the end of that sub didn't work because there was another macro that ran after this from the same button click that initiated this code and it was causing the issue as soon as I clicked it, but I just didn't see the result until the macro ended. But anyway, you were correct...me not labeling my sheets was the issue. I used your same suggestion of code in a different place and that made all the difference. I will think about calling out the sheets more often.

As for the advanced tip. How are you naming the tabs such that another user changing the name doesn't break the code? I know how to name cells, or groups of cells, but not sheets. I tried in the name manager function of the formula bar but couldn't figure it out.

**edit:
That is why I run the code "Application.ScreenUpdating = True" because I like to see what my code is doing. It is sometimes hard to trace when things are happening if I can't follow them happening in real time.

[SamT]

How are you naming the tabs such that another user changing the name doesn't break the code?I'm not naming the Tabs.


Technical definitions:
Tab Name = Sheet Name (appears on Sheet Tab)
CodeNAme = Sheet Object Name (Only seen in the VBA PRoject Explorer. Must be edited in the VBA Properties Window)

In THe VBA Editor, Press Ctrl+R to see the Project Explorer, and Press F4 to see the Properties Window.

In the Project Explorer, Sheet names will be in the Format CodeName (Tab Name)
In the Properties Window the CodeName Property Name will be in Parentheses, And the "Tab" Name Property Name will not be in Parens.

22192

I blame Joel Spolsky for the confusion... Why not call it CodeName in the Properties Window?!?!?!