Run Time Error 5 help

[HYKE]

Hi,

Kindly help me find the reason why this code is giving me run time error 5.

Private Sub Oft_Change()
Dim prodName As Range
Dim mobProd As Range
Dim emProd As Range
Dim ws As Worksheet
Set ws = Worksheets("LookupLists")
    Select Case Oft
        Case "BPME"
        
        'Worksheets("CONSOLIDATED").Select
        Me.Spreadsheet1.Sheets("Sheet1").Range("A1:AJ10").Value = Worksheets("BPME").Range("A1:AJ10").Value
        Me.Spreadsheet1.Sheets("Sheet1").Activate
        Me.Spreadsheet1.Sheets("Sheet1").Name = "BPME"
        Worksheets("BPME").Select
        Me.cProd.Visible = True
        Me.moProdbox.Visible = False
        Me.emProdbox.Visible = False
        For Each prodName In ws.Range("prodName")
            With Me.cProd
                 .AddItem prodName.Value
                 .List(.ListCount - 1, 1) = prodName.Offset(0, 1).Value
            End With
        Next prodName
        
        Case "EXXON MOBIL"
        Me.Spreadsheet1.Sheets("Sheet2").Range("A1:AJ10").Value = Worksheets("EXXONMOBIL").Range("A1:AJ10").Value
        Me.Spreadsheet1.Sheets("Sheet2").Activate
        Me.Spreadsheet1.Sheets("Sheet2").Name = "EXXONMOBIL"
        Worksheets("EXXONMOBIL").Select
        Me.cProd.Visible = False
        Me.moProdbox.Visible = True
        Me.emProdbox.Visible = False
        For Each mobProd In ws.Range("mobProd")
            With Me.moProdbox
                 .AddItem mobProd.Value
                 .List(.ListCount - 1, 1) = mobProd.Offset(0, 1).Value
            End With
        Next mobProd
        Case "EMARAT"
        
        Me.Spreadsheet1.Sheets("Sheet3").Range("A1:AJ10").Value = Worksheets("EMARAT").Range("A1:AJ10").Value
        Me.Spreadsheet1.Sheets("Sheet3").Activate
        Me.Spreadsheet1.Sheets("Sheet3").Name = "EMARAT"
        Worksheets("EMARAT").Select
        Me.cProd.Visible = False
        Me.moProdbox.Visible = False
        Me.emProdbox.Visible = True
        For Each emProd In ws.Range("emProd")
            With Me.emProdbox
                 .AddItem emProd.Value
                 .List(.ListCount - 1, 1) = emProd.Offset(0, 1).Value
            End With
        Next emProd
        
    End Select
End Sub

When I select the first case to the third case the code works fine, but whenever I am going to return to 1st case or select 2nd case from 3rd case the run time error appears and highlights this part of the code.

Me.Spreadsheet1.Sheets("Sheet1").Range("A1:AJ10").Value = Worksheets("BPME").Range("A1:AJ10").Value

Can you please help me understand why?

Thanks,

HYKE

kindly forgive my codes, I am just not really into VBA..

 

[Greg Truby]

My guess would be that either Sheets("Sheet1")'s name has changed or else Worksheets("BPME")'s name has changed by the time you're in the second pass through.

 

[HYKE]

Hi Greg,

Thank you very much! Your guess is right. I had the userform spreadsheet name changed on userform initialize, then remove this line from the combobox

Me.Spreadsheet1.Sheets("Sheet1").Name = "BPME"

now the code works fine, I can now switched between selections without encountering this runtime error 5. Thanks again.

HYKE

 

[HYKE]

Just one more question though, Is it possible to add additional/insert sheet into userform spreadsheet? If so, how? I tried right clicking the tab of the spreadsheet, but there is no option to add or insert a new sheet.

 

[Greg Truby]

Wow! I think Bill Clinton may have still been in office the last time I put a spreadsheet control on a userform. I'm assuming this is an OWC spreadsheet control. At least on the version of OWC-SS control that I can pull up look for the Commands and Options button on the top of the spreadsheet control. On mine it's the second-to-last button, next to the "?" button for help. Click that and then on the Workbook tab you'll see button to Insert, Delete or Hide sheets or reorder sheets.

 

[HYKE]

Hi Greg, thanks. If this userform spreadsheet had been here for a long time, have you any suggestion for a better way to display worksheet data on the userform and be able to manipulate the data on the form. Also, is there any option on the spreadsheet where I can wrap the text in the cell?

Thanks again..

 

[Greg Truby]

In looking at the help file for ss objects, I do not see any listing for a .WrapText property. Nor do I see it among the formatting controls. So I'm thinking that's not available.

As far as your other question "better way to display workshet data on a userform" - the question is too broad to answer definitively. In general, the basic rule is to "let forms be forms and let worksheets be worksheets". As with anything there are exceptions. In my circumstances I was using the ss control to allow the user to mark how to parse data getting imported from a terminal emulation screenshot. So there wasn't really a better way. It didn't make any sense to attempt to use an actual worksheet to perform the task. The application had much better flow using a control.

So the real question is "does it make more sense to do this in a spreadsheet control or in an actual worksheet?" And the answer may indeed be "yes".

One issue that I did run into years ago was that OWC was new and not always installed on every user's machine. I don't know if those libraries are part of standard installations now-a-days or not. I would hope so, but it is something to keep in the back of your mind should you try to run the code on a different machine and find that it's bombing in seemingly random places. That's a sign of a busted reference (missing library).