Object variable or with block variable not set trying to run macro from CommandButton

Question

I've done a summary workbook of some workbooks located in some different drives which I want to update (as they rely on INDIRECT in my workbook) by simply opening the files, and in extension update all values (hopefully). What I've tried is to implement a CommandButton through Developer tab and ActiveX Controls. I then right click this button and "View code" where I simply got "Private Sub CommandButton21_Click" and "End Sub" which I gather means that the "button's name" is CommandButton21.

My code looks like this:

Private Sub CommandButton21_Click()
    Dim WbookCheck As Workbook
    Dim sPath As String, sFile As String

On Error Resume Next
    sPath = "C:\Pathtofile"
    sFile = sPath & "filename.xlsx"
    Set WbookCheck = Workbooks(sFile)
On Error GoTo 0
    If CommandButton21.Value = True Then
        If WbookCheck Is Nothing Then 'Closed
            Workbooks.Open Filename:=sFile, ReadOnly:=True
        ElseIf Application.ActiveWorkbook.Name = WbookCheck.Name Then
            WbookCheck.Close SaveChanges:=True
        Else
            WbookCheck.Activate
        End If
    Else
        WbookCheck.Close True
    End If
End Sub

Does anybody have a clue regarding this?

At a quick glance, it looks like there needs to be a backslash between the path and filename. Maybe change to: sPath = "C:\Pathtofile\` Does that help? — basodre
– basodre, Commented Mar 13, 2015 at 12:44
This helped in this project as well as in another project so thanks! — Cenderze
– Cenderze, Commented Mar 17, 2015 at 11:36

Rory · Accepted Answer · 2015-03-13 15:13:17Z

1

I don't know why you are trying to test the value of a commandbutton, but it will be False so the attempt to close a workbook variable that was never assigned will fail. You should skip the Value test and use:

Private Sub CommandButton21_Click()
    Dim WbookCheck As Workbook
    Dim sPath As String, sFile As String

On Error Resume Next
    sPath = "C:\Pathtofile\"
    sFile = sPath & "filename.xlsx"
    Set WbookCheck = Workbooks(sFile)
On Error GoTo 0

    If WbookCheck Is Nothing Then 'Closed
        Workbooks.Open Filename:=sFile, ReadOnly:=True
    ElseIf Application.ActiveWorkbook.Name = WbookCheck.Name Then
        WbookCheck.Close SaveChanges:=True
    Else
        WbookCheck.Activate
    End If
End Sub

answered Mar 13, 2015 at 15:13

Rory

34.2k5 gold badges40 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Cenderze Over a year ago

Thanks! Sorry for being late with a response. I've been occupied with other projects. This works like a charm however! I originally took the code from some website in order to learn VBA. Originally there was no CommandButton I Believe but rather Another type of button, hence the invalid comparison I believe. Thanks again though!

Collectives™ on Stack Overflow

Object variable or with block variable not set trying to run macro from CommandButton

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related