1

I am currently working on coding button in which I found problem when I tried to click at button. the message window runtime error 1004, application-defined or object-defined error is pop up.

here is my code:

Private Sub CommandButton1_Click()
    Dim s As Single
    Dim t As Integer
    Application.ScreenUpdating = False
    s = Worksheets("Sheet2").Range("J6").Value
    s = s * 1.1
    Worksheets("Sheet2").Range("J6").Value = s
    Worksheets("Sheet2").Range("K6").Value = 0
    Worksheets("Sheet2").Range("L6").Value = Date

    With ActiveWorkbook.Worksheets("Sheet2").Sort
    .SortFields.Clear
    .SortFields.Add Key:=Range("I6"), _
    SortOn:=xlSortOnValues, _
    Order:=xlAscending, _
    DataOption:=xlSortNormal
    .SetRange Range("F6:L11")
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With

    Dim r As Single
    Dim count As Integer
    Dim i As Integer
    count = 6
    For i = 1 To 6
        count = i + 5
        r = ActiveWorkbook.Worksheets("Sheet2").Range("I" & count).Value
        If r <= 0.7 Then

           ActiveWorkbook.Worksheets("Sheet2").Range(Cells(count, 6), Cells(count, 12)).Select
           With Selection.Interior
           .Pattern = xlSolid
           .PatternColorIndex = xlAutomatic
           .ThemeColor = xlThemeColorAccent6
           .TintAndShade = 0.599993896298105
           .PatternTintAndShade = 0
           End With
        End If
     Next i    

    Application.ScreenUpdating = True
End Sub

this code is coding on Sheet1. However when I tried to create sub procedure on This Workbook object is appeared to work as what I want

back on code on Sheet1, when I put "'" in front of the following line ActiveWorkbook.Worksheets("Sheet2").Range(Cells(count, 6), Cells(count, 12)).Select it's turn green and there is no error window, but it still doesn't work as I want it to

I would love to thank in advance to anyone who pass by and provide me any comment or solution. and I have to sorry for my incompetence coding, I am beginner.

Fresher
  • 894
  • 1
  • 7
  • 27
blackthorn
  • 45
  • 4
  • Working for me, with Excel 2013. I just have to replace *Sheet2* with *Feuil2* since I have a french Excel. But I have to put your code in a Workbook-wide module, otherwise I get the same *error 1004*. –  Jul 02 '14 at 06:08
  • Mr.Jean-Claude Arbaut, thank you for your comment. :] I think you may want to look at answer below if you want to try to fix that error. work like magic... coding always like magic to me :3 – blackthorn Jul 02 '14 at 06:30

2 Answers2

1

Instead of:

ActiveWorkbook.Worksheets("Sheet2").Range(Cells(count, 6), Cells(count, 12)).Select

use:

With ActiveWorkbook.Worksheets("Sheet2")
        Call .Activate
        Call .Range(.Cells(count, 6), .Cells(count, 12)).Select
End With

Like this you clearly specify that you want to select a range defined by the cells of ActiveWorkbook.Worksheets("Sheet2"), not by some cells from whatever object active at the moment of the execution. Selection does not work in an inactive sheet.

  • @user3702656 Glad I could help. If I may suggest, remember the general idea: you cannot select in something that wasn't activated. –  Jul 02 '14 at 06:30
  • Thank you again TT^TT /me bow deeply – blackthorn Jul 02 '14 at 06:38
  • [Try to avoid the .Select](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) –  Jul 02 '14 at 08:20
0

If you are getting this Error for a web-application developed on Classic ASP, then change your settings on IIS.

  1. Open IIS.
  2. Go to Application Pools and right click on your app-pool.
  3. Go to Advanced Settings
  4. Under Process Model, set Identity = LocalSystem.

I hope this would help, as it did for me

Agilanbu
  • 2,747
  • 2
  • 28
  • 33
Matthew
  • 1