Hi folks,
I am getting a run time error # 9 when I run a macro that calls a Userform or when I try to run code in a Userform module. The code performs beautifully on my computer, but it did not work on a coworker's computer. It ended up working on 3 out of the 5 computers I have tried it on.
I have tried changing security settings to low, and a bunch of other stuff, but I cannot get the code to run on the computers that get the run time error on them when I try running the code on them.
I get the run time error when I try to load or show any userform in the workbook and I get it if I try to run code that is in the userform module. However, if I paste the code into a regular module and run it, the code runs fine.
Does anyone know what could be causing this? I don't think my code is causing the problem since it runs on some machines, I am guessing there is a setting that is preventing Excel from calling Userforms. Any ideas?
Thanks,
Joe
I have a button, Private Sub CommandButton1_Click, on a sheet that shows a userform. This is the bit of code that gets tagged with the run time error. The userform has a refedit control on it allows the user to select a cell and then hit ok to run the code or cancel to, well, cancel it.
Code for the button that gets tagged with the run time error:
Code in the Userform Module:
Private Sub cmdCancel_Click()
TradeTicketSpreadsheetName = ThisWorkbook.Name
Unload Me
Workbooks(TradeTicketSpreadsheetName).Activate
End Sub
Private Sub cmdLoad_Click()
Dim SelRange As Range
Dim Addr As String
Dim LandingSheet As Object
TradeTicketSpreadsheetName = ThisWorkbook.Name
'get the address from the RefEdit control
Addr = frmLoadTrade.refTrade.Value
'Set the SelRange object to the range specified in the refedit control
Set SelRange = Range(Addr)
tradesrow = SelRange.Row
With Workbooks("TradeDB").Sheets("TradeDB").Range("1:1")
vehicle_column = .Find("Vehicle").Column
notification_date_column = .Find("Notification Date").Column
trade_date_column = .Find("Trade Date").Column
ticket_number_column = .Find("Ticket Number").Column
seneca_trader_name_column = .Find("Seneca Trader Name").Column
seneca_trader_phone_column = .Find("Seneca Trader Phone").Column
seneca_trader_fax_column = .Find("Seneca Trader Fax").Column
sell_code_column = .Find("Sell Code").Column
End With
'Code for assigning values to the Ranges in the tradeticket
Vehicle = Cells(tradesrow, vehicle_column)
date_notification = Cells(tradesrow, notification_date_column)
date_trade = Cells(tradesrow, trade_date_column)
ticket_number = Cells(tradesrow, ticket_number_column)
seneca_trader_name = Cells(tradesrow, seneca_trader_name_column)
seneca_trader_phone = Cells(tradesrow, seneca_trader_phone_column)
seneca_trader_fax = Cells(tradesrow, seneca_trader_fax_column)
sell_code = Cells(tradesrow, sell_code_column)
'Code to copy variable values to named ranges in trade ticket
Workbooks(TradeTicketSpreadsheetName).Activate
Range("vehicle") = Vehicle
Range("date_notification") = date_notification
Range("date_trade") = date_trade
Range("ticket_number") = ticket_number
Range("seneca_trader_name") = seneca_trader_name
'Range("seneca_trader_phone") = seneca_trader_phone
'Range("seneca_trader_fax") = seneca_trader_fax
Range("sell_code") = sell_code
Unload Me
End Sub
Private Sub UserForm_Initialize()
Call OpenDB 'module 1 - opens TradeDB workbook if it is not already open
Workbooks("TradeDB").Activate
End Sub
Display More