0

I recorded a macro which has been working for at least a week but is no longer working for some reason. Today when I went through my workbook to make sure everything was working, I received an "Application-defined or Object-defined error" message when selecting a macro from a drop down menu. I don't understand why a macro would work and then stop working all of a sudden let alone how a recorded macro could stop working. The workbook I am working on is a project budget template. I have a drop down menu with each selection running macros in the first tab (SERVICES is the second tab). The macro below is one of the macros that are called from several of the drop down selections. Different projects have different roles and rates - the macro below is to change the roles for a different type of project.

I'm sorry for posting a question without trying anything but honestly don't know even know how to start. I'm newer to VBA so this is quite confusing for me.

Sub CompanyARolestoRolesColumnInServicesTab()
    Call TurnOffFunctions

' CompanyA_AddRolesAtoRolesColumnInServicesTab Macro

    Sheets("SERVICES").Select
    Range("C29").Select
    Application.CutCopyMode = False
    Application.CutCopyMode = False
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=$K$374:$K$417"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    Selection.Copy
    Range( _
        "C30:C48,C50:C69,C71:C90,C92:C111,C113:C132,C134:C153,C155:C174,C176:C195,C197:C216,C218:C237,C239:C258,C260:C279,C281:C300,C302:C321,C323:C342,C344:C363" _
        ).Select
    Range("C344").Activate
    ActiveSheet.Paste
    Range("A1").Select
    Sheets("SOW").Select
Call NoSelect
Call TurnOnFunctions
End Sub

The section of the macro which becomes highlighted after the error message and after pressing debug is this:

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=$K$374:$K$417"
CTEAGUE212
  • 45
  • 6
  • 2
    That line seems fine to me. What is going on in the `$K$374:$K$417` range? – GSerg Aug 20 '19 at 10:31
  • If this code winds up needing fixing, you may wish to read [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba), and unnecessarily redundant repetition (such as calling `Application.CutCopyMode = False` twice in a row) – Chronocidal Aug 20 '19 at 10:33
  • Your code works perfect for me. Could be that any of the cells in range `K374:K417` is hidden or merged? Or maybe any of the values in that range shows an error message? – Foxfire And Burns And Burns Aug 20 '19 at 10:54
  • Is the worksheet protected? – Rory Aug 20 '19 at 11:55
  • There are three tabs total in this workbook and all three are protected, @Rory. Sorry for not thinking to include that. – CTEAGUE212 Aug 20 '19 at 12:12
  • @Foxfire And Burns And Burns, that range (rows K374:K417) is in white font so that it is hidden to anyone who uses this project budget workbook. – CTEAGUE212 Aug 20 '19 at 12:18
  • @GSerg, I'm guessing there is somehow an application-defined or object-defined error in that range since VBA says this is the part of the macro that needs fixing when I try to run it. – CTEAGUE212 Aug 20 '19 at 12:21
  • You'll need to unprotect the sheet, or allow editing objects. – Rory Aug 20 '19 at 12:26
  • Ok, @Rory. I can't unprotect the sheet since some things need to be locked when sent to others to use this budget template. I know how to allow editing objects if I manually protect sheets but since I am using a macro to auto protect the worksheets, what line of code can I add to the protect workbook macro in order to allow editing objects? Here is the macro I am using to protect the worksheets: ```Private Sub Workbook_Open() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Protect Password:="$0W", UserInterFaceOnly:=True Next ws End Sub``` – CTEAGUE212 Aug 20 '19 at 12:43
  • You need to add the `drawingobjects:=true` argument. I should note that in my experience this is not 100% reliable when using the `Userinterfaceonly` argument. – Rory Aug 20 '19 at 12:48
  • Thanks, @Rory. It doesn't seem to be working, I guess it is because of the Userinterfaceonly argument. I will try will taking that argument out. – CTEAGUE212 Aug 20 '19 at 12:53
  • My mistake - that should be False, not True! (If you take that (UIO) out, you'll need to actually unprotect and reprotect in the code. – Rory Aug 20 '19 at 12:58
  • Another option would be putting the values of the dropdown list on an independent worksheet, and then hide that worksheet. And when I say hide, I mean `xlSheetVeryHidden`. Check https://stackoverflow.com/a/853315/9199828 The only way users could access to that data is using VBA editor. – Foxfire And Burns And Burns Aug 20 '19 at 13:01
  • @Foxfire And Burns And Burns - I appreciate your suggestion but this budget template needs to be used for an entire company, many of which aren't advanced Excel users so I couldn't make it so using the VBA editor is the only choice. – CTEAGUE212 Aug 21 '19 at 02:22
  • No worries, @Rory. Thanks for your help. It's still not working so I will have to take the UIO out. To protect and unprotect, would I just make two macros like this ```Sub Protect_Sheet() Sheets("Sheet1").Protect "YourPassword", True, True End Sub``` (one for protect and the other for unprotect) and then each placed respectively at the begining and end of the protect workbook macro? – CTEAGUE212 Aug 21 '19 at 06:17

0 Answers0