Run Time Error 9 when Calling a Userform

Joe Dawson


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 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

Andy Pope


Hi,

Error 9 is a 'Subscript out of range'. so for example referencing a worksheet that does not exit will raise that error.

MsgBox Worksheets("Sheet99").Name

or trying to access an element of an array beyond the bounds of the array.

Dim vArray(3) As Variant

vArray(4) = 1

But without seeing any of your code it's pretty hard to guess exactly whats wrong.

Joe Dawson

Hi,

Thanks for the response. Here is the code:

Private Sub CommandButton1_Click()
frmLoadTrade.Show 'calls userform
End Sub

The code is so simple I did not think it would be helpful to post before. The form exists and the code and the userform run fine on 2 people's computers at work and my computer at home, but on 2 other people's computers at work the code gets the Run Time error. So, I dont think it is the code, but I don't know what the problem is.

Joe

Andy Pope

As you say that is a simple piece of code :) which is not the cause but the entry point to the problem.

Can you check the userform itself of any code in either the Initialize or Activate event.

Joe Dawson

Thanks for checking back. Here is a more complete picture of the code and the flow. Please let me know if you see anything that sticks out as the culprit.

Joe

The user clicks a button on one of the sheets which calls a userform:

Private Sub CommandButton1_Click()
frmLoadTrade.Show 'calls userform
End Sub

I get the run time error #9 on the line coded with red font on some computers but not others.

The userform has three controls on it, a button to cancel, a refedit to allow the user to select a row, and an enter button. Here is the code behind the userform:

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

Andy Pope

The problem is this line in the Initialize event

Workbooks("TradeDB").Activate

The reason is works some of the time is that some of the PC have the option to hide known file extension types.

So the file TradeDB.xls will sometimes open with that name and sometimes with just TradeDB.

Including the .xls should work in either case.

Workbooks("TradeDB.xls").Activate

Joe Dawson

Bingo. That got the job done. Thanks for the help. It is odd that the code worked on some computers but not on others.

I changed all of the code so I refer to workbooks with the .xls extension after them and the code works on all of the computers I have tried it on.

Joe