Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
My macro doesn't work if sheet is protected but it works if not protected
Runtime error 91: Object variable or With block variable not set
My code is Private Sub ComboBox1_Change() Dim fso As Object Set fso = CreateObject("Scripting.FileSystemObject") ruta = ActiveWorkbook.Pathimagen = 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
My macro doesn't work if sheet is protected but it works if not protected
Hi,
Am Tue, 11 Feb 2014 07:52:43 -0800 (PST) schrieb : Cells.Find(What:=Replace(imagen, ".jpg", "")).Select <<----((ERROR HERE) Label3 = ActiveCell.Offset(0, 1) Label2 = ActiveCell.Offset(0, 2) try: set c =Cells.Find(What:=Replace(imagen, ".jpg", "")) Label3 = c.Offset(0, 1) Label2 = c.Offset(0, 2) .. .. Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
My macro doesn't work if sheet is protected but it works if not protected
I tried your code but the error is now he
Set c = Cells.Find(What:=Replace(imagen, ".jpg", "")) Label3 = c.Offset(0, 1) <<<--- ERROR HERE Label2 = c.Offset(0, 2) Label12 = c.Offset(0, 10) Label13 = c.Offset(0, 11) Label14 = c.Offset(0, 12) Label15 = c.Offset(0, 13) Label16 = c.Offset(0, 14) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
My macro doesn't work if sheet is protected but it works if not protected
Hi,
Am Tue, 11 Feb 2014 09:05:17 -0800 (PST) schrieb : Set c = Cells.Find(What:=Replace(imagen, ".jpg", "")) Label3 = c.Offset(0, 1) <<<--- ERROR HERE change it to: Set c = Cells.Find(What:=Replace(imagen, ".jpg", "")) if not c is nothing then Label3 = c.Offset(0, 1) Label2 = c.Offset(0, 2) Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
My macro doesn't work if sheet is protected but it works if not protected
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
UDF doesn't work on a protected sheet in Excel 2003 | Excel Discussion (Misc queries) | |||
UDF doesn't work on a protected sheet in Excel 2003 | Excel Discussion (Misc queries) | |||
Find doesn't work if the sheet is protected | Excel Discussion (Misc queries) | |||
Macro doesn't work when sheet protected | Excel Programming | |||
Macro doesn't work on UserInterfaceOnly-protected sheet | Excel Programming |