Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

unprotect a sheet with VBA

I have about 100 files (each one sheet) that I have built a macro to do various thins to. I got through 50 of them only to find that the remaining 50 have been protected by the originator of the files. There is no password involved, they are simply protected. what code do i use in the beginning of my macro to automatically unprotect the sheets so the macro can run?


Post your answer or comment

comments powered by Disqus
Hi, I have a button that is supposed to unprotect a sheet and unhide some columns then unload two forms. For the most part it works. It unprotects the sheet, unhides the columns and closes both forms, but form some reason, the sheet is still protected once all of that is done.

Private Sub YesButton_Click()

    With ActiveSheet
        .Unprotect Password:="password"
        .Columns("S:AF").Hidden = False
    End With
    
    Unload frmFileStatus
    Unload frmHR
    
End Sub
Thanks!

Hi all

Hope someone can help. I am working on a sheet with VBA but cant get it to work. This is my first trie with VBA.

I need to be able to group/ungroup rows in a sheet when it is protected. All sheets needs to have the code. My password will be lets say "Stormdronk"

Hope this is clear, thanks so much for the help!!!

Hi, I know how to protect/unprotect a sheet with code, but what is the code if you want to do it to a workbook?

Thanks,
Jeff

Hello

I know how to manually protect a sheet with a password, but my question is this: How do I protect a certain sheet with a password using VBA code?

From my present knowledge when I say:

then it protects my sheet but with no password, so any user of my sheet could just unprotect it manually by going to Tools
> Protect > Unprotect Sheet

Any hints?

Hi all,

I was wondering if there was an easy way to select a group of sheets from VBA. Basically I have a workbook with 64 sheets named 1..64 (Note: There are a variable number of other sheets in the workbook) and I would like an easy way of selecting all these sheets with VBA code.

Can anybody help me out?

Thanks

Hi experts,

one question about moving in a sheet with VBA...

I used WINDOW / FREEZE PANES, so now lines 1-10 are frozen

I have named 3 cells :
TOP1 is cell A 101
TOP2 is cell A 201
TOP3 is cell A 301

I have created 3 buttons, for 3 macros (GOTOTOP1, GOTOTOP2, GOTOTOP3).
I would like GOTOTOP1 to put row 101 just below the frozen area
I would like GOTOTOP2 to put row 102 just below the frozen area
I would like GOTOTOP2 to put row 102 just below the frozen area
Preferentially using the named cells

How do I do that ?

Thanks,
Regards,

H.

is it possible to protect and unprotect a sheet using VBA ?

I have a sheet i would like to prevent users from editing by using protection, but would also like VBA script to be able to paste rows onto the sheet - so would need VBA to unprotect, paste the data, and then protect again.

Can this be done?

Hi people

I have a book in excel with a lot of information about my clients and I wanna make a form with vba and put there some checkboxes to use as a filter (Name, city, pay, not pay ,etc...) and I wanna push a button and the results from that filter apears in a separate sheet, so having this filtration I'll use word to get these clients and make a serie printing to send them a letter without write the names and adress one by one. can you help me?
I'll be very gratefull

Hi,

I created an module update utility that will replace an old module with a new one that is shipped over with the utility. It works perfectly except for one thing: I password-protected the VB Projects distributed along with my spreadsheet. As far as I know, we can't unprotect VB Projects with VBA code. But if by any off chance any of you expert knows how a way to do it, I'll greatly appreciate if you'll share with me.

Thanks,
spacecityguy@yahoo.com

I've been able to get a macro to unprotect a sheet that doesn't have a password and re-protect it without a password.

ActiveSheet.Unprotect

ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
        False, AllowFormattingRows:=True, AllowInsertingRows:=True, AllowSorting _
        :=True, AllowFiltering:=True

However, If a password is placed on a sheet, is there a way to use VBA to unprotect and re-protect using that password?

Thanks!

Hi to all, I'm new to this forum. I have a really simple question: what's the right method to assign a value to a cell with VBA?
I have an Excel file with multiple VBA functions, but the most simple (theorically speaking) is making me crazy: I need to assign a value to a cell of one of the sheets, but... I cannot! The sheet is named "TOTALE", the cell is the G7. This is my function (it checks if in a table of the sheet "Foglio" there are some data highlighted and return the number of them. If they are highlighted with pink, it writes the numbers of them in the cell G7 of the sheet TOTALE):
Function GiocTotale(Row1 As Integer, ColStart As Integer, Row2 As Integer, ColEnd As Integer, Foglio As String)

Dim Contatore As Integer, i As Integer

Contatore = 0

For i = ColStart To ColEnd
    If Worksheets(Foglio).Cells(i, Row1).Interior.ColorIndex <> xlColorIndexNone And Trim(Worksheets(Foglio).Cells(i,
Row1)) <> Empty Then
        Contatore = Contatore + 1
        If Worksheets(Foglio).Cells(i, Row1).Interior.ColorIndex = 38 Then
Worksheets("TOTALE").Row("G7").Value = Worksheets("TOTALE").Row("G7").Value + 1
    End If
    If Worksheets(Foglio).Cells(i, Row2).Interior.ColorIndex <> xlColorIndexNone And Trim(Worksheets(Foglio).Cells(i,
Row2)) <> Empty Then Contatore = Contatore + 1
Next i

GiocTotale = Contatore

End Function
Without the Worksheets("TOTALE").Row("G7").Value = Worksheets("TOTALE").Row("G7").Value + 1 the function work perfectly. With it, I get a #VALUE error. I tried everything, but it didn't work. =__=

Hi

Can anyone tell me whether it is posisble to move a chart to a certain position in the sheet with VBA?

I am trying to use a macro to create a chart in excel and move it to the left side of the screen.

Thanks

Jeskit

The following is a schedule we have for our church daycare. We have three groups of kids. Is there a way with VBA or a formula to create a schedule from this schedule so that the three groups are not doing the same thing at the same time. Except for Breakfast, Snack Time and Lunch. I hope this made since. If not I will try to explain better.

6:00 a.m.-7:15 a.m. open center / free time
7:15 a.m.-7:30 a.m. bathroom break / wash hands
7:30 a.m.-8:00 a.m. breakfast
8:00 a.m.-8:15 a.m. clean-up / wash hands
8:15 a.m.-8:30 a.m. story / group time
8:30 a.m.-9:45 a.m. free play
9:45 a.m.-10:00 a.m. bathroom break / wash hands
10:00 a.m.-10:15 a.m. snack time
10:15 a.m.-10:25 a.m. bathroom break
10:25 a.m.-11:15 a.m. outside / gross motor activity
11:15 a.m.-11:45 a.m. craft time
11:45 a.m.-12:00 p.m. clean-up / bathroom break
12:00 p.m.-12:30 p.m. lunch
12:30 p.m.-3:00 p.m. nap
3:00 p.m.-3:30 p.m. wake-up / bathroom break / quiet time
3:30 p.m.-4:00 p.m. free play / center time
4:00 p.m.-4:10 p.m. bathroom break / wash hands
4:10 p.m.-4:30 p.m. snack time
4:30 p.m.-6:00 p.m. free choice / outside ( pick up time )

Thanks
Gary

Hello!

I want to create a formula in cell B16 in a worksheet with VBA code. The command is like below:


	VB:
	
Worksheets("Administration").Activate 
Range("B16").Formula = "=if(b17="";"";Vlookup(b17;Hjälpblad!e16:f515;2)) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The formula works like it should when I type it in cell b16. But creating it with VBA like above does not work. I get an error when executing the code. Any other formula works fine but not this one. If I - for example - write VBA code

	VB:
	
"Range("B16").Formula = "=b18+b19" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
it works without a problem.

I guess the "-signs screws it up. Is there another way of doing this?

Thanks!

Best regards
Jens

Is there a way I can add formulas dynamically to a sheet using VBA? I need to do cost calculations in the excel sheet for each company defined as an input from the user, so the number of formulas needed will change? Is there a way to write in the formulas to the sheet? Thanks.

Hello all, I'm racking my brain trying to find out how to use a cell value in a formula with VBA.

For example, I have the following code:

FinalRow = Cells(65536, 1).End(xlUp).Row
CYGP = Cells(Final Row, 11).Column
Cells(FinalRow + 2, CYGP).Select

I now need to be able to use the value that's located in the selected cell elsewhere in the spreadsheet. I've tried the following:

ActiveCell.FormulaR1C1 = "=R[FinalRow]C[CYGP]

This was an attempt to put the value that's in Cells(FinalRow, CYGP) into my active cell, but it's not working...Can any of you VBA gurus please help?

I have been going in circles with this for long enough. I have a task to create pivot tables, 4 on a sheet with VBA. The size is unknown so I can't use a static or fixed position for the destination of th 2nd, 3rd, and 4th.

All I want to do is find the last row number - simple enough, or so it ought to be.

This is the closest I have got:

Set PT = PTcache.CreatePivotTable(TableDestination:="", TableName:="MyPvt")
debug.print PT.TableRange1.Address

I do get the range as a result:
$A$3:$G$16

I wanted to retrieve the bottom row and add 3 to it for the next Pivot table.

I don't think I should have to come up with a function to parse that string. Any comments? Please help, somebody.

Thank you.

If there a command to see if a sheet is protected.

I basically want to be able to disable a button is the sheet is protected and enable it if the sheet is unprotected. I know how to protect/unprotect a sheet using VBA but is there a test to check if the sheet is already protected.

How to insert a formula in a cell with VBA
example : if formula is "=if(a3=2;a3;a2)"
i used
cells(2,3).Formula="=if(a3=2;a3;a2)"

bu i receive an error
Application object ....

if i used "=s4"
everything is ok.

I was wondering if there is a simpler method to my madness. I have a value in column "D" that is a company name, next to it I have a blank for a company ID. Sheet 2 in column "B" I have a company name, and in "A" I have a company ID. How can I have it look at Sheet 1 company then compare it to Sheet2 and then return the ID to the blank on Sheet1? Here is what I have written, but it takes forever to run it this way and was wondering if there is a simpler and faster method. Bare with me I am semi-noob at the keyboard...

With the below code, I am basically copying the value over to Sheet2 then doing the search, if it matches a value on sheet2 then it copies the value for the ID and pastes it over to Sheet1. It works but takes about 10 minutes to run on a sheet with about 600 lines. Any help would be greatly appreciated.

Sub Find()
Application.ScreenUpdating = False
Dim wrksht1 As String
Dim wrksht2 As String

wrksht1 = "SW_UPLOAD"
wrksht2 = "VNDR LISTING"

Sheets(wrksht1).Select
Range("E2").Select

ActiveCell.Offset(0, -1).Copy
Sheets(wrksht2).Select
ActiveSheet.Range("c2").PasteSpecial xlPasteValues
ActiveSheet.Range("B2").Select

Do
Sheets(wrksht2).Select
If ActiveCell.Value = ActiveSheet.Range("C2").Value Then
    ActiveCell.Offset(0, -1).Copy
    Sheets(wrksht1).Select
    ActiveCell.PasteSpecial xlPasteValues
    ActiveCell.Offset(1, 0).Select
    ActiveCell.Offset(0, -1).Copy
    Sheets(wrksht2).Select
    ActiveSheet.Range("C2").PasteSpecial xlPasteValues
    ActiveSheet.Range("B2").Select
ElseIf ActiveCell.Value = BLANK Then
    Sheets(wrksht1).Select
    ActiveCell.Value = "No Match Found"
    ActiveCell.Offset(1, 0).Select
    ActiveCell.Offset(0, -1).Copy
    Sheets(wrksht2).Select
    ActiveSheet.Range("C2").PasteSpecial xlPasteValues
    ActiveSheet.Range("B2").Select
ElseIf ActiveCell.Value <> Range("C2").Value Or ActiveCell.Value = BLANK Then
    ActiveCell.Offset(1, 0).Select
    
End If

Sheets(wrksht1).Select
Loop Until ActiveCell.Offset(0, -1).Value = BLANK
Application.ScreenUpdating = True
End Sub


Hello Group!

Let me describe my problem briefly...

For Instance, I select a cell sheet1!A1 and another sheet2!B5... If I put anything in sheet2!B5, it will automatically update sheet1!A1 and if I put sheet1!A1 then I will update sheet2!B5...

Theme:

I've a sheet and it has an outlook all the calculations are done there... and I have another sheet which has almost the same values with a different outlook.

So if I update any values of any of the sheet it will update the other sheet. And if I update the 2nd sheet then the 1st one will be updated. Though the both have total different look. And the links in sheet1 and sheet2 are random... i.e. sheet1!A5 -> sheet2!B3; sheet1!C6 -> sheet2!A10 goes on...

The main thing is, all I want is A Mirror Copy of a Sheet with Different Outlook

Thanks in Advance

Hi there,

I was wondering if there was any way to reference a sheet with a variable name instead of needing to know the exact name of the sheet?

I am trying to open an individual sheet into my workbook with the code:

Dim sFileName As String
'Show the open dialog and pass the selected _
file name To the String variable "sFileName"
sFileName = Application.GetOpenFilename
'They have cancelled.
If sFileName = "False" Then Exit Sub
     
Workbooks.Open fileName:=sFileName
Sheets("EVENTS").Copy After:=Workbooks("NLeapGIS10.xls").Sheets(ThisWorkbook.Sheets.Count)
Workbooks("EVENTS.xls").Close
That works fine, if the sheet the person is trying to open is called "EVENTS". However I would like to implement something like

Sheets(sFileName)

to open the name of the sheet the same as the file name.

I need a macro that will take the name in cell A2 and see if there is a sheet
that already has that name. If there is not a sheet then I need it to create
a new sheet with the name as the name of the sheet. Is this possible?

my macro right now just searches for a sheet with the same name.

If Cells(1, 2).Value <> "" Then
name = Cells(1, 2).Value
Sheets(name).Activate

how can I add the creating new sheet part?

Thanks,
Kyle

How can I set up a sheet with locked and frozen column headings that can be sorted by excel? When I protect row 1 then sorting is disabled from the data menu.


No luck finding an answer? You could always try Google.