EXCEL VBA - RUN TIME ERROR '91': OBJECT VARIABLE OR WITH BLOCK VARIABLE NOT SET

%3CLINGO-SUB%20id%3D%22lingo-sub-2098389%22%20slang%3D%22en-US%22%3EEXCEL%20VBA%20-%20RUN%20TIME%20ERROR%20'91'%3A%20OBJECT%20VARIABLE%20OR%20WITH%20BLOCK%20VARIABLE%20NOT%20SET%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2098389%22%20slang%3D%22en-US%22%3E%3CP%3EExcel%20(Version%2016.45%5Bmicrosoft%20365%5D)%26nbsp%3B%3C%2FP%3E%3CP%3EmacOS%20Big%20Sur%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Geniuses!%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20trying%20to%20put%20together%20a%20contact%20manager%20document%20in%20Excel%20and%20I'm%20running%20into%20a%20problem%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CU%3EI%20want%20to%20had%20two%20macros%3C%2FU%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1-%20Make%20a%20macro%20to%20open%20a%20file%20picker%2C%20choose%20a%20picture%20and%20put%20the%20link%20in%20column%20%22L%22%20(on%20the%20same%20row%20of%20the%20contact%20I'm%20adding%20the%20picture%20to)%20and%20in%20N4%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E2-%20Make%20a%20macro%20that%20is%20going%20to%20show%20the%20picture%20when%20the%20contact%20is%20selected.%20(If%20there%20is%20a%20link%20in%20N4%2C%20display%20the%20picture%2C%20if%20not%2C%20do%20nothing)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20the%20code%20I%20have%26nbsp%3B%3C%2FP%3E%3CP%3E________________________________________________________________________________________________________%3C%2FP%3E%3CP%3ESub%20Cont_AttachThumb()%3C%2FP%3E%3CP%3EDim%20PicFile%20As%20FileDialog%3C%2FP%3E%3CP%3EWith%20Sheet2%3C%2FP%3E%3CP%3ESet%20PicFile%20%3D%20Application.FileDialog(msoFileDialogFolderPicker)%3C%2FP%3E%3CP%3EWith%20PicFile%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%3CFONT%20color%3D%22%23FF0000%22%3E.Title%20%3D%20%22Select%20a%20Contact%20Picture%22%20%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B.Filters.Add%20%22All%20Picture%20Files%22%2C%20%22.jpg%2C*jpeg%2C*.gif%2C*.png%2C*bmp%2C*tiff%22%2C%201%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BIf%20.Show%20%26lt%3B%26gt%3B%20-1%20Then%20GoTo%20NoSelection%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BSheet2.Range(%22N4%22).Value%20%3D%20.SelectedItems(1)%20'Put%20File%20name%20in%20N4%3C%2FP%3E%3CP%3EEnd%20With%3C%2FP%3E%3CP%3EIf%20.Range(%22B3%22).Value%20%3D%20False%20Then%20.Range(%22L%22%20%26amp%3B%20Sheet2.Range(%22B2%22).Value).Value%20%3D%20.Range(%22N4%22).Value%20Cont_DisplayThumb%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENoSelection%3A%3C%2FP%3E%3CP%3EEnd%20With%3C%2FP%3E%3CP%3EEnd%20Sub%3C%2FP%3E%3CP%3E______________________________________________________________________________________________________%3C%2FP%3E%3CP%3ESub%20Cont_DisplayThumb()%3C%2FP%3E%3CP%3EDim%20PicPath%20As%20String%3C%2FP%3E%3CP%3EWith%20Sheet2%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BOn%20Error%20Resume%20Next%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B.Shapes(%22ThumbPic%22).Delete%20'Delete%20thumbnail%20picture%20(if%20any)%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BOn%20Error%20GoTo%200%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BPicPath%20%3D%20.Range(%22N4%22).Value%20'Picture%20Path%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BWith%20.Pictures.Insert(PicPath)%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20With%20.ShapeRange%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B.LockAspectRation%20%3D%20msoTrue%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B.Height%20%3D%2080%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B.Name%20%3D%20%22ThumbPic%22%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BEnd%20With%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20End%20With%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20With%20.Shapes(%22ThumbPic%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20.Left%20%3D%20Sheet2.Range(%22J5%22).Left%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20.Top%20%3D%20Sheet2.Range(%22J5%22).Top%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20.IncrementLeft%20-20%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20.IncrementTop%2010%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BEnd%20With%3C%2FP%3E%3CP%3EEnd%20With%3C%2FP%3E%3CP%3EEnd%20Sub%3C%2FP%3E%3CP%3E_________________________________________________________________________________________________%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20when%20I%20run%20it%2C%20the%20code%20that%20pops-up%20(In%20the%20title)%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERUN%20TIME%20ERROR%20'91'%3A%20OBJECT%20VARIABLE%20OR%20WITH%20BLOCK%20VARIABLE%20NOT%20SET%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eand%20It%20highlights%20the%20.Title%3D...%20in%20the%20first%20macro%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EN.B.%20see%20attached%20Contact%20MANAGER%20File%3C%2FP%3E%3CP%3E____________________________________________________________________________________________%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20should%20I%20do%20next%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20help%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2098389%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2098973%22%20slang%3D%22en-US%22%3ERe%3A%20EXCEL%20VBA%20-%20RUN%20TIME%20ERROR%20'91'%3A%20OBJECT%20VARIABLE%20OR%20WITH%20BLOCK%20VARIABLE%20NOT%20SET%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2098973%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F943342%22%20target%3D%22_blank%22%3E%40Michetu%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThere%20are%20several%20problems%3A%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3EThe%20folder%20picker%20dialog%20doesn't%20allow%20you%20to%20specify%20a%20filter.%20I%20think%20you%20meant%20to%20use%20the%20open%20dialog%20or%20the%20file%20picker.%3C%2FLI%3E%0A%3CLI%3EYou%20omitted%20the%20.%20between%20*%20and%20some%20of%20the%20extensions.%3C%2FLI%3E%0A%3CLI%3EThe%20extensions%20must%20be%20separated%20by%20semicolons%20instead%20of%20commas.%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3ESub%20Cont_AttachThum()%0A%20%20%20%20Dim%20PicFile%20As%20FileDialog%0A%20%20%20%20Set%20PicFile%20%3D%20Application.FileDialog(msoFileDialogOpen)%0A%20%20%20%20With%20PicFile%0A%20%20%20%20%20%20%20%20.Title%20%3D%20%22Select%20a%20Contact%20Picture%22%0A%20%20%20%20%20%20%20%20.Filters.Add%20%22All%20Picture%20Files%22%2C%20%22*.jpg%3B*.jpeg%3B*.gif%3B*.png%3B*.bmp%3B*.tiff%22%2C%201%0A%20%20%20%20%20%20%20%20If%20.Show%20%26lt%3B%26gt%3B%20-1%20Then%20GoTo%20NoSelection%0A%20%20%20%20%20%20%20%20Sheet2.Range(%22N4%22).Value%20%3D%20.SelectedItems(1)%20'Put%20File%20name%20in%20N4%0A%20%20%20%20End%20With%0A%20%20%20%20With%20Sheet2%0A%20%20%20%20%20%20%20%20If%20.Range(%22B3%22).Value%20%3D%20False%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20.Range(%22L%22%20%26amp%3B%20.Range(%22B2%22).Value).Value%20%3D%20.Range(%22N4%22).Value%0A%20%20%20%20%20%20%20%20End%20With%0A%20%20%20%20End%20With%0A%20%20%20%20Cont_DisplayThumb%0A%0ANoSelection%3A%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
New Contributor

Excel (Version 16.45[microsoft 365]) 

macOS Big Sur

 

Hi Geniuses! 

 

I'm trying to put together a contact manager document in Excel and I'm running into a problem: 

 

I want to had two macros

 

1- Make a macro to open a file picker, choose a picture and put the link in column "L" (on the same row of the contact I'm adding the picture to) and in N4

 

2- Make a macro that is going to show the picture when the contact is selected. (If there is a link in N4, display the picture, if not, do nothing)

 

This is the code I have 

________________________________________________________________________________________________________

Sub Cont_AttachThumb()

Dim PicFile As FileDialog

With Sheet2

Set PicFile = Application.FileDialog(msoFileDialogFolderPicker)

With PicFile

         .Title = "Select a Contact Picture"

         .Filters.Add "All Picture Files", ".jpg,*jpeg,*.gif,*.png,*bmp,*tiff", 1

         If .Show <> -1 Then GoTo NoSelection

         Sheet2.Range("N4").Value = .SelectedItems(1) 'Put File name in N4

End With

If .Range("B3").Value = False Then .Range("L" & Sheet2.Range("B2").Value).Value = .Range("N4").Value Cont_DisplayThumb

 

NoSelection:

End With

End Sub

______________________________________________________________________________________________________

Sub Cont_DisplayThumb()

Dim PicPath As String

With Sheet2

             On Error Resume Next

             .Shapes("ThumbPic").Delete 'Delete thumbnail picture (if any)

             On Error GoTo 0

             PicPath = .Range("N4").Value 'Picture Path

             With .Pictures.Insert(PicPath)

                  With .ShapeRange

                       .LockAspectRation = msoTrue

                       .Height = 80

                       .Name = "ThumbPic"

                   End With

              End With

              With .Shapes("ThumbPic")

                      .Left = Sheet2.Range("J5").Left

                      .Top = Sheet2.Range("J5").Top

                      .IncrementLeft -20

                      .IncrementTop 10

               End With

End With

End Sub

_________________________________________________________________________________________________

 

The when I run it, the code that pops-up (In the title) 

 

RUN TIME ERROR '91': OBJECT VARIABLE OR WITH BLOCK VARIABLE NOT SET

 

and It highlights the .Title=... in the first macro

 

N.B. see attached Contact MANAGER File

____________________________________________________________________________________________

 

What should I do next

 

Please help 

6 Replies

@Michetu 

There are several problems:

  • The folder picker dialog doesn't allow you to specify a filter. I think you meant to use the open dialog or the file picker.
  • You omitted the . between * and some of the extensions.
  • The extensions must be separated by semicolons instead of commas.
Sub Cont_AttachThum()
    Dim PicFile As FileDialog
    Set PicFile = Application.FileDialog(msoFileDialogOpen)
    With PicFile
        .Title = "Select a Contact Picture"
        .Filters.Add "All Picture Files", "*.jpg;*.jpeg;*.gif;*.png;*.bmp;*.tiff", 1
        If .Show <> -1 Then GoTo NoSelection
        Sheet2.Range("N4").Value = .SelectedItems(1) 'Put File name in N4
    End With
    With Sheet2
        If .Range("B3").Value = False Then
            .Range("L" & .Range("B2").Value).Value = .Range("N4").Value
        End With
    End With
    Cont_DisplayThumb

NoSelection:
End Sub

Thank you so much for your time. 

 

Athough it is still not working, I think it is because this version of excel is "sandboxed" 

Seems I'm having issues accessing files that are not in the 365 series...

 

Is that a known problem/issu? 

 

@Hans Vogelaar 

@Michetu 

Does this work?

 

Sub Cont_AttachThum()
    Dim PicFile As FileDialog
    Set PicFile = Application.FileDialog(msoFileDialogOpen)
    With PicFile
        .Title = "Select a Contact Picture"
        .Filters.Add "All Picture Files", "*.jpg;*.jpeg;*.gif;*.png;*.bmp;*.tiff", 1
        If .Show <> -1 Then GoTo NoSelection
        Sheet2.Range("N4").Value = .SelectedItems(1) 'Put File name in N4
    End With
    With Sheet2
        If .Range("B3").Value = False Then
            .Range("L" & .Range("B2").Value).Value = .Range("N4").Value
        End If
    End With
    Cont_DisplayThumb

NoSelection:
End Sub

Sub Cont_DisplayThumb()
    Dim PicPath As String
    Dim pic As Picture
    With Sheet2
        On Error Resume Next
        .Shapes("ThumbPic").Delete  'Delete thumbnail picture (if any)
        On Error GoTo 0
        PicPath = .Range("N4").Value 'Picture Path
        Set pic = .Pictures.Insert(PicPath)
        With pic.ShapeRange
            .LockAspectRatio = msoTrue
            .Height = 80
            .Name = "ThumbPic"
            .Left = Sheet2.Range("J5").Left - 20
            .Top = Sheet2.Range("J5").Top + 10
        End With
    End With
End Sub
Same error '91' on the
.Title = ...

@Michetu 

Strange - it works for me (Excel 2019 32-bit on Windows 10 Home 64-bit).

I have attached my version.

Thank you for your time. 

 

Still doesn't work. 

 

I'll try to run a different excel version @Hans Vogelaar