With statement produces Run-Time Error 438

Question

I get

Run-time error '438'
"Object doesn't support this property or method."

I was using a code that activated another workbook (Changes_Database Workbook), then (inside of the Changes_Database Workbook there is a sheet called Changes) the code inserts a row and shifts the others down, copies the format of the cells below, and then inputs a key, part and process name (descriptions basically, unimportant) alongside the date and time.

The code below is very slow:

Sub NewPart2()

'Sets Changes_Database as active contents and unprotects

    Set Cd = Workbooks.Open(Filename:="\\FILEPATH\Technology_Changes\Changes_Database_IRR_200-2S_New.xlsm", Password:="Swarf")
    Set Changes = Cd.Sheets("Changes")

Changes.Activate
ActiveSheet.Unprotect "Swarf"

'Selects the 2nd row of the database, which is the row after the headings
ActiveSheet.Rows("2:2").Select

'Inserts a new row and shifts the other rows down
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow

'Inputs the key that is being added to the new row
ActiveSheet.Range("A2").Value = Sheet1.Range("H4").Value

'Inputs the part and process name to the new row
ActiveSheet.Range("D2").Value = UCase(Sheet1.Range("E4").Value)
ActiveSheet.Range("E2").Value = Sheet1.Range("E5").Value

'Inputs the date and time for when it was added
ActiveSheet.Range("B2").Value = Now
ActiveSheet.Range("C2").Value = Now
ActiveSheet.Range("C2").NumberFormat = "h:mm:ss AM/PM"
ActiveSheet.Range("B2").NumberFormat = "dd/mm/yyyy"

'On Error Resume Next

            ActiveSheet.Protect "Swarf"
            ActiveWorkbook.Save
            ActiveWorkbook.Close SaveChanges:=True

On Error Resume Next

End Sub

Activating the other sheet is taking quite a long time for this module to execute it's function, so I attempted a With statement but I get that error.

I am trying to improve the speed of this code with my second code: (SCREENSHOTS OF BOTH CODES AS WELL CAN BE FOUND BELOW)

Sub NewPart2()

Application.ScreenUpdating = False

Set y = Workbooks.Open(Filename:="\\FILEPATH\Technology_Changes\Changes_Database_IRR_200-2S_New.xlsm", Password:="Swarf")

    With y

      Sheets("Changes").Unprotect "Swarf"

        .Sheets("Changes").Rows("2:2").Select
        'Inserts a new row and shifts the other rows down
        .Sheets("Changes").Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow

        'Inputs the key that is being added to the new row
        .Sheets("Changes").Range("A2").Value = Sheet1.Range("H4").Value

        'Inputs the part and process name to the new row
        .Sheets("Changes").Range("D2").Value = UCase(Sheet1.Range("E4").Value)
        .Sheets("Changes").Range("E2").Value = Sheet1.Range("E5").Value

        'Inputs the date and time for when it was added
        .Sheets("Changes").Range("B2").Value = Now
        .Sheets("Changes").Range("C2").Value = Now
        .Sheets("Changes").Range("C2").NumberFormat = "h:mm:ss AM/PM"
        .Sheets("Changes").Range("B2").NumberFormat = "dd/mm/yyyy"

      Password = "Swarf"

        .Save
        .Close False

    End With

Application.ScreenUpdating = True

End Sub

Don't use `Selection` - you don't need to `Select`. You're also missing a period in front of `Sheets("Changes").Unprotect "Swarf"`. — BigBen, Dec 04 '19 at 20:12
^^^^^^^ [this may help](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — cybernetic.nomad, Dec 04 '19 at 20:12
@BigBen what is an alternative to using Select? and from my understanding, I purposely left out the period in front of Sheets("Changes").Unprotect "Swarf" because it only needs to be inside the with statement (I tested this) — Handreen, Dec 04 '19 at 20:15
No. The period *must* be there. Being inside the with statement doesn't mean anything. It "works" - not really, there is an implicit `ActiveWorkbook` - you're not actually qualifying that the sheet is indeed in `y`. Adding the period actually qualifies this. — BigBen, Dec 04 '19 at 20:15

BigBen · Accepted Answer · 2019-12-04T20:26:01.970

3

Don't use (or attempt to use) Selection. A Worksheet or Sheet does not have a Selection property.

Change

.Sheets("Changes").Rows("2:2").Select
'Inserts a new row and shifts the other rows down
.Sheets("Changes").Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow

to

.Sheets("Changes").Rows("2:2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow

Side note, you use Sheets("Changes") so frequently that you could just use that with your With...End With and save a lot of typing.

Set y = Workbooks.Open(Filename:="\\FILEPATH\Technology_Changes\Changes_Database_IRR_200-2S_New.xlsm", Password:="Swarf")

With y.Sheets("Changes")
    .Rows("2:2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
    ' and so on
End With

y.Save
y.Close False

Very Important: Make sure you qualify Rows and Range calls within the With...End With by adding a period . beforehand.

edited Dec 04 '19 at 20:26

answered Dec 04 '19 at 20:14

BigBen

46,229
7
24
40

Okay I have made those suggested changes, my only issue is that it's giving me a Run-time error on the .Save for some reason? any help with this? – Handreen Dec 04 '19 at 20:24
1

That should be `y.Save` - because it is `Workbook.Save`. Same for `y.Close`. See the edit. – BigBen Dec 04 '19 at 20:25
You are correct, thank you for your patience! One last issue (if you know about this), when I open the second workbook to check it, the very first time I attempt to open it, it automatically says "The password you supplied is not correct. Verify that the CAPS LOCK key is off and be sure to use the correct capitalization" This is weird because I literally don't click or type anything, I don't even see the Password prompt on the first try (or after I close it and try to reopen, I get this caps lock message then have to click the workbook again to open it and get the Password prompt) – Handreen Dec 04 '19 at 20:32
Hmm I'm not sure, this seems like an unrelated issue, but still an issue for sure. – BigBen Dec 04 '19 at 20:33
1

@Handreen the `Workbooks.Open` function call is supplying a hard-coded "Swarf" password. Remove this `Password: "Swarf"` argument, or change the string literal to match the actual password. – Mathieu Guindon Dec 04 '19 at 20:34
@MathieuGuindon thanks for your explanation, how can I avoid the hard-coded password and change the string literal to become my password? – Handreen Dec 06 '19 at 12:55

With statement produces Run-Time Error 438

1 Answers1