Sheets("Sheet Name").select (not working)

hi all

im trying to stwitch sheets with VBA and when i do the code stops,

Sheets("Sheet Name").select

is the method im using, i can use this exact same method for another sheet within my excel

for example

Sheets("Database").select

will work fine

but Sheets("Sign Preview").select

does not

no matter what i try, iv even renamed the sheet with no effect

the error i get is: Run Time Error '32809': Application-defined or object-defined error.

my buttons that start the code are on user forms.

the code used to work :S and does for other sheets...

how do i solve this / whats caused it to stop working.

regards

Andrew

its definetly the sheets.select that it stops on. but here is my code

Private Sub cmdPreview_Click()

Dim wb As Workbook
    Dim ws As Worksheet
    Dim TxtRng  As Range

    Set wb = ActiveWorkbook
    Set ws = wb.Sheets("Create A Sign Data")
    Sheets("Database").Select
    
    Application.ScreenUpdating = False
    
    ModelNumber = ActiveSheet.Cells(ActiveCell.Row, 1)
    POSProductCode = ActiveSheet.Cells(ActiveCell.Row, 2)
    Brand = ActiveSheet.Cells(ActiveCell.Row, 3)
    OnlinePrice = ActiveSheet.Cells(ActiveCell.Row, 50)
    RRP = ActiveSheet.Cells(ActiveCell.Row, 49)
    TagCost = ActiveSheet.Cells(ActiveCell.Row, 51)
    LaserOrInk = ActiveSheet.Cells(ActiveCell.Row, 4)
    MonoOrColour = ActiveSheet.Cells(ActiveCell.Row, 5)
    OptPrint = ActiveSheet.Cells(ActiveCell.Row, 6)
    OptScan = ActiveSheet.Cells(ActiveCell.Row, 7)
    OptCopy = ActiveSheet.Cells(ActiveCell.Row, 52)
    OptFax = ActiveSheet.Cells(ActiveCell.Row, 8)
    OptUSB = ActiveSheet.Cells(ActiveCell.Row, 53)
    OptWiFi = ActiveSheet.Cells(ActiveCell.Row, 10)
    OptWiFiDirect = ActiveSheet.Cells(ActiveCell.Row, 54)
    OptEthernet = ActiveSheet.Cells(ActiveCell.Row, 12)
    OptNFC = ActiveSheet.Cells(ActiveCell.Row, 11)
    OptAirPrint = ActiveSheet.Cells(ActiveCell.Row, 21)
    OptAndriodPrint = ActiveSheet.Cells(ActiveCell.Row, 20)
    OptWebPrint = ActiveSheet.Cells(ActiveCell.Row, 56)
    PaperSize = ActiveSheet.Cells(ActiveCell.Row, 13)
    PrintQuality = ActiveSheet.Cells(ActiveCell.Row, 14)
    DuplexPrint = ActiveSheet.Cells(ActiveCell.Row, 15)
    PaperCapacity = ActiveSheet.Cells(ActiveCell.Row, 16)
    MonoPrintSpeed = ActiveSheet.Cells(ActiveCell.Row, 17)
    ColourPrintSpeed = ActiveSheet.Cells(ActiveCell.Row, 18)
    ScanResolution = ActiveSheet.Cells(ActiveCell.Row, 19)
    DuplexScan = ActiveSheet.Cells(ActiveCell.Row, 55)
    CustomFeature1 = ActiveSheet.Cells(ActiveCell.Row, 22)
    CustomFeature2 = ActiveSheet.Cells(ActiveCell.Row, 23)
    CustomFeature3 = ActiveSheet.Cells(ActiveCell.Row, 24)
    
    
    BlackCart = ActiveSheet.Cells(ActiveCell.Row, 28): BlackYeild = ActiveSheet.Cells(ActiveCell.Row, 36): BlackPrice = ActiveSheet.Cells(ActiveCell.Row, 44)
    CyanCart = ActiveSheet.Cells(ActiveCell.Row, 25): CyanYeild = ActiveSheet.Cells(ActiveCell.Row, 33): CyanPrice = ActiveSheet.Cells(ActiveCell.Row, 41)
    MagentaCart = ActiveSheet.Cells(ActiveCell.Row, 26): MagentaYeild = ActiveSheet.Cells(ActiveCell.Row, 34): MagentaPrice = ActiveSheet.Cells(ActiveCell.Row, 42)
    YellowCart = ActiveSheet.Cells(ActiveCell.Row, 27): YellowYeild = ActiveSheet.Cells(ActiveCell.Row, 35): YellowPrice = ActiveSheet.Cells(ActiveCell.Row, 43)
    PGBlackCart = ActiveSheet.Cells(ActiveCell.Row, 29): PGBlackYeild = ActiveSheet.Cells(ActiveCell.Row, 37): PGBlackPrice = ActiveSheet.Cells(ActiveCell.Row, 45)
    GreyCart = ActiveSheet.Cells(ActiveCell.Row, 32): GreyYeild = ActiveSheet.Cells(ActiveCell.Row, 40): GreyPrice = ActiveSheet.Cells(ActiveCell.Row, 48)
    LightMagentaCart = ActiveSheet.Cells(ActiveCell.Row, 30): LightMagentaYeild = ActiveSheet.Cells(ActiveCell.Row, 38): LightMagentaPrice = ActiveSheet.Cells(ActiveCell.Row, 46)
    LightCyanCart = ActiveSheet.Cells(ActiveCell.Row, 31): LightCyanYeild = ActiveSheet.Cells(ActiveCell.Row, 39): LightCyanPrice = ActiveSheet.Cells(ActiveCell.Row, 47)
    
    DateToDisplay2 = ActiveSheet.Cells(ActiveCell.Row, 59)
    ProductCareTerm = ActiveSheet.Cells(ActiveCell.Row, 57)
    ADFCapacity = ActiveSheet.Cells(ActiveCell.Row, 58)
    
    Sheets("Create A Sign Data").Select

    Range("B1").Value = Brand
    Range("B2").Value = ModelNumber
    Range("B3").Value = POSProductCode
    Range("B5").Value = OnlinePrice
    Range("B6").Value = RRP
    Range("B7").Value = TagCost
    Range("B9").Value = LaserOrInk
    Range("B11").Value = MonoOrColour
    Range("B13").Value = OptPrint
    Range("B14").Value = OptScan
    Range("B15").Value = OptCopy
    Range("B16").Value = OptFax
    Range("B18").Value = OptUSB
    Range("B19").Value = OptWiFi
    Range("B20").Value = OptWiFiDirect
    Range("B21").Value = OptEthernet
    Range("B22").Value = OptNFC
    Range("B23").Value = OptAirPrint
    Range("B24").Value = OptAndriodPrint
    Range("B25").Value = OptWebPrint
    Range("B27").Value = PaperSize
    Range("B28").Value = PrintQuality
    Range("B29").Value = DuplexPrint
    Range("B30").Value = PaperCapacity
    Range("B31").Value = MonoPrintSpeed
    Range("B32").Value = ColourPrintSpeed
    Range("B34").Value = ScanResolution
    Range("B35").Value = DuplexScan
    Range("B48").Value = CustomFeature1
    Range("B49").Value = CustomFeature2
    Range("B50").Value = CustomFeature3
    Range("B36").Value = ADFCapacity
    
    Range("B39").Value = BlackCart: Range("C39").Value = BlackYeild: Range("D39").Value = BlackPrice
    Range("B40").Value = CyanCart: Range("C40").Value = CyanYeild: Range("D40").Value = CyanPrice
    Range("B41").Value = MagentaCart: Range("C41").Value = MagentaYeild: Range("D41").Value = MagentaPrice
    Range("B42").Value = YellowCart: Range("C42").Value = YellowYeild: Range("D42").Value = YellowPrice
    Range("B43").Value = PGBlackCart: Range("C43").Value = PGBlackYeild: Range("D43").Value = PGBlackPrice
    Range("B44").Value = GreyCart: Range("C44").Value = GreyYeild: Range("D44").Value = GreyPrice
    Range("B45").Value = LightMagentaCart: Range("C45").Value = LightMagentaYeild: Range("D45").Value = LightMagentaPrice
    Range("B46").Value = LightCyanCart: Range("C46").Value = LightCyanYeild: Range("D46").Value = LightCyanPrice
        
    Range("B52").Value = DateToDisplay2
    Range("B55").Value = ProductCareTerm
    
'STOPS HERE
    Sheets("Sign Preview").Select
    
    frmPreviewSign.Show
    
    Application.ScreenUpdating = True

End Sub

if i declare the worksheet and workbook, then it will pass this line of code. but then the next lien of code


frmpreviewsign.show gives the same error.

i checked the names carefully even removed spaced and renamed it.

seems the sheet has become Corrupt, the only way to fix it is to remove it, and replace it with a previous version of the sheet.

i think i will just have to rework this all with a previous version of the file.

Hi Andrew,

That happened to me. It's related to the KB 2553154 released from Microsoft, on dec 9th, spoiling ActiveX controls. Even when you apply the MS Fix, workbooks saved in this Excel Versions (2010: 14.0.7140 or 2013: 15.0.4675) don't work. When you use sheets("xxx").select an error occurs, if the sheet has an activeX control. I had to recreat my workbooks.

See http://www.infoworld.com/article/285...root-cert.html

Regards,
Marcelo Rychter