Forum Discussion

Michetu's avatar
Michetu
Copper Contributor
Jan 26, 2021

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

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...
excel
Macros and VBA
office 365
HansVogelaar's avatar
HansVogelaar
MVP
Jan 26, 2021

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
Michetu's avatar
Michetu
Copper Contributor
Jan 26, 2021

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? 

 

HansVogelaar 

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jan 26, 2021

    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
    • Michetu's avatar
      Michetu
      Copper Contributor
      Jan 26, 2021
      Same error '91' on the
      .Title = ...

Resources