Run Time Error 9 when Calling a Userform

Run Time Error 9 when Calling a Userform

• NeedSomeAnswers

  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.
  
  
  

• ahmedsaad4

  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
  

  
  

• T.Ramesh

  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
  
  

• kart

  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.
  
  
  
  

• Richard_Wolf

  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
  
  

• Keren S

  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
  
  
  
  
• Run Time Error 9 when Calling a Userform