I am trying to use an add photo option in an excel sheet. The error reads:
Error: 1004 - The item withe the specified name wasn't found.
The second error after clicking OK reads:
Error: 91 - Object variable of With block variable not set
After clicking on the OK button 9-10 times, it finds the file I am looking for. The file worked last week and now it doesn't. I did not change any of the coding, but it appears the coding has changed. See the coding listed below. I was told the following in another forum:
It looks like you've been affected by a problem in a recent Microsoft Update that stops you from creating ActiveX controls. Near the end of your code, it tries to create a button and then refer to it. These are the lines that are triggering the two error messages you get.
You will see the same problem in any file that has this code.
I tried the microsoft fix it tool and it did not fix this problem. Any suggestions or help is greatly appreciated. If I can't fix it, I am going to re-image the computer.
Private Sub InsertPhoto()
Dim Count As Integer
Dim InsertRow As Integer
Dim TgtRow As Integer
Dim CmdBtnNbr As Integer
Dim Loc As Range
Dim btn As CommandButton
Dim sObjName As String
Dim sPassword As String
Dim RangeAddr As String
On Error GoTo InsertPhoto_Error
' Suspend screen updates and unlock the sheet
Application.ScreenUpdating = False
sPassword = wksData.Range("$A$4").Value
wksPhotos.Unprotect Password:=sPassword
'Retrieve the current picture count.
Count = wksData.Range("PicCount").Value + 1
If Count > 50 Then
MsgBox "Maximum photo count met.", vbInformation + vbOKOnly, "MBA Property Inspection Form"
Application.ScreenUpdating = True
wksPhotos.Protect Password:=sPassword, DrawingObjects:=True, Contents:=True, Scenarios:=True
Exit Sub
End If
' Select the Catagory dropdown for the last photo. This prevents
' Excel 2003 from locking up if a photo is selected.
'
If Count > 1 Then
wksPhotos.Range(apNamePropInspCat & Right("0" & Count - 1, 2)).Select
End If
'Now get the row where we're going to do our insertion.
' We'll insert two rows above the add button
Set Loc = wksPhotos.cmdAddPic.TopLeftCell
InsertRow = Loc.Row - 2
'Now compute the cell for our copy destination
RangeAddr = "B" & InsertRow
' Copy
wksResources.Range("nrPhotoBlock").Copy
wksPhotos.Range(RangeAddr).Insert Shift:=xlDown
' Add the ActiveX Change Photo button
TgtRow = InsertRow + apPhotoChgBtnOffset
sObjName = "CommandButton" & GetBtnNbr(Count)
wksPhotos.OLEObjects.Add ClassType:="Forms.CommandButton.1"
Set btn = wksPhotos.OLEObjects(sObjName).Object
With btn
.Left = Range("AK" & TgtRow).Left + 1
.Top = Range("AK" & TgtRow).Top
.Width = Range("AK" & TgtRow & ":AL" & TgtRow).Width - 2
.PrintObject = False
.Caption = "Change Photo " & Count
End With