Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

VBA WORKS WITH F8 BUT NOT WHEN RUN

0

Hi all have the following code

Sub SaveSheetToExResidents()
Dim ws As Worksheet
Set ws = ActiveSheet
ws.Unprotect Password:="debra"
ws.Range("A1:O76").ExportAsFixedFormat xlTypePDF, Filename:= _
"C:\Users\Tonyg\Desktop\Altair Tony\Ex Residents\" & ws.Range("F76").Value & ws.Range("D76") & ws.Range("C76").Value, Openafterpublish:=False
ws.Range("D7:G10").ClearContents
ws.Range("I8:L10").ClearContents
ws.Range("K14:N16").ClearContents
ws.Range("H19:M24").ClearContents
ws.Range("C13:I16").ClearContents
ws.Range("C19:F23").ClearContents
ws.Range("C26:I26").ClearContents
ws.Range("C34:K37").ClearContents
ws.Range("C44:L66").ClearContents
ws.Range("E69:I74").ClearContents
ws.Range("C29:K31").ClearContents
ws.Protect Password:="debra", UserInterfaceOnly:=True
ws.Range("C76").Value = ws.Range("C76").Value Mod 99 + 1
Sheet196.Protect Password:="debra", UserInterfaceOnly:=True
Sheet196.Activate
End Sub

If i use F8 the above works fine, it saves to desired folder and finally returns to sheet requested. However when I run the macro, it only returns to final sheet. It doesn't save to folder or clear contents from worksheet.

What am i doing wrong?

excel
posted by: mycobblermends (rep: 6) on Sat Jul 9, 2022 at 1:06 am
Answer
Discuss

Discussion

Please edit your question to remove most capitals from its title- we really dislike being shouted at!

VBA and F8 should be the only caps. Thanks.
John_Ru (rep: 6142) Jul 9, '22 at 5:51 am
Add to Discussion

Answers

0

Hi Tony

Firstly are you sure the ActiveSheet (ws) uses the provided password? If not (and it's protected) the clear lines can't work. 

You didn't attach a sample file but I created a similar file (with different sheets, ranges, folders) and it ran when I stepped through it using F8 and when I ran the macro again (by clicking play) on that modified sheet

If failed however if I ran it from a sheet where the password wasn't "debra" or when there was nothing in the cells which make up the file name in your line:

ws.Range("A1:O76").ExportAsFixedFormat xlTypePDF, Filename:= _.... & ws.Range("F76").Value & ws.Range("D76") & ws.Range("C76").Value,

I'm not sure why you open we with that password but go on to set it on:

Sheet196.Protect Password:="debra", UserInterfaceOnly:=True
Sheet196.Activate

You might need to change that to:

ws.Protect Password:="debra", UserInterfaceOnly:=True

before activating the final sheet (if 196 is that)

Hope this helps.

posted by: John_Ru (rep: 6142) on Sat Jul 9, 2022 at 5:50 am
Discuss

Discussion

Hi John 
thanks for your answer. My code worked if using F8 and it also worked if i played it.
it was when i assigned the macro to a button that it did not work. I finally figured out why.  I  had accidently assigned a link to the button as well. When I removed the link from the button, it worked as expedcted. Sorry that I wasted your time and in future I will definetly edit out most capitals out of titles.
mycobblermends (rep: 6) Jul 9, '22 at 8:48 am
Okay, no problem. Glad you fixed it.

It would be good if you could edit this title in that way (please) 
John_Ru (rep: 6142) Jul 9, '22 at 9:07 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login