Runtime error 91: Object variable or With block variable not set

  • Thread starter Thread starter anapaulags92
  • Start date Start date
A

anapaulags92

My code is

Private Sub ComboBox1_Change()

Dim fso As Object

Set fso = CreateObject("Scripting.FileSystemObject")
ruta = ActiveWorkbook.Path
imagen = ComboBox1.List(ComboBox1.ListIndex) & ".jpg"
ruta_e_imagen = ruta & "\FOTOS\" & imagen

If fso.FileExists(ruta_e_imagen) Then
Image1.Picture = LoadPicture(ruta_e_imagen)


Cells.Find(What:=Replace(imagen, ".jpg", "")).Select <<<<------(ERROR HERE)

Label3 = ActiveCell.Offset(0, 1)
Label2 = ActiveCell.Offset(0, 2)
Label12 = ActiveCell.Offset(0, 10)
Label13 = ActiveCell.Offset(0, 11)
Label14 = ActiveCell.Offset(0, 12)
Label15 = ActiveCell.Offset(0, 13)
Label16 = ActiveCell.Offset(0, 14)

Else

MsgBox "La Imagen: " & imagen & ", NO está disponible"

End If

End Sub
 
Try...

Private Sub ComboBox1_Change()

Dim fso As Object, rngFound As Range
Dim ruta$, imagen$, ruta_e_imagen$ 'type=String

Set fso = CreateObject("Scripting.FileSystemObject")
ruta = ActiveWorkbook.Path
imagen = ComboBox1.List(ComboBox1.ListIndex) & ".jpg"
ruta_e_imagen = ruta & "\FOTOS\" & imagen

If fso.FileExists(ruta_e_imagen) Then
Image1.Picture = LoadPicture(ruta_e_imagen)
Set rngFound = Cells.Find(What:=Replace(imagen, ".jpg", ""))
If rngFound Is Nothing Then GoTo NotFound

With rngFound
Label3 = .Offset(0, 1): Label2 = .Offset(0, 2)
Label12 = .Offset(0, 10): Label13 = .Offset(0, 11)
Label14 = .Offset(0, 12): Label15 = .Offset(0, 13)
Label16 = .Offset(0, 14)
End With 'rngFound
Else
GoTo NotFound
End If

NormalExit:
Exit Sub

NotFound:
MsgBox "La Imagen: " & imagen & ", NO está disponible"
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
GS said:
Hey Garry, do me a favor. Kill that line, if possible.

Uh.., that was anoying me but didn't think anyone would care. I think
it's gone now! Thanks for the nudge! ;-)

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
My macro doesn't work if sheet is protected but it works if not protected what can i do?
 
From a previous post...

There's no reason to toggle protection when you reset protection when
the file is first opened. Make sure you set 'UserInterfaceOnly:=True'
so your code can modify the sheet but the user can't via the UI. Make
sure your code includes 'AllowFiltering:=True' OR set
'worksheet.EnableAutoFilter:=True'...

Dim wks As Variant
For Each wks In ActiveWorkbook.Worksheets
wks.Unprotect <password>
wks.Protect Password:=<password>, UserInterfaceOnly:=True, _
AllowFiltering:=True '//add more as req'd
Next 'wks

It's feasible, however, that not all sheets need/are protected in a
file so you may want to use a string constant of delimited sheetnames
that protection is applied to...

Const sSheetsToProtect$ = "Sheet1,Sheet2,Sheet4"
Dim vName As Variant
For Each wks In Split(sSheetsToProtect$, ",")
Sheets(vName).Unprotect <password>
Sheets(vName).Protect Password:=<password>, _
UserInterfaceOnly:=True, _
AllowFiltering:=True '//add more as req'd
Next 'wks

HTH

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
I think something is wrong in my code to protect and no to protect:

Code:


Sub protegida () (TO PROTECT)
Application.ScreenUpdating = False

pw = "protegida"
Hoja1.Select

For i = 1 To Sheets.Count
Sheets(i).Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowSorting:=treu, AllowFiltering:=True, AllowUsingPivotTables:=True
Next i

Dim wsSheet As Worksheet
For Each wsSheet In ActiveWorkbook.Worksheets
If wsSheet.Name <> Hoja1.Name Then
wsSheet.Visible = xlSheetVeryHidden
End If
Next wsSheet



End Sub

Sub edicion()(NOT TO PROTECT)

Application.ScreenUpdating = False
Hoja1.Select

For i = 1 To Sheets.Count
Sheets(i).Protect DrawingObjects:=False, Contents:=False, Scenarios:=False _
, AllowSorting:=False, AllowFiltering:=False, AllowUsingPivotTables:=False
Next i



Dim wsSheet As Worksheet

For Each wsSheet In ActiveWorkbook.Worksheets
If wsSheet.Name <> Hoja1.Name Then
wsSheet.Visible = xlSheetVisible
End If

Next wsSheet

End Sub
 
Back
Top