worksheet pass vba

Working with Worksheets using Excel VBA (Explained with Examples)

-- By Sumit Bansal

Apart from cells and ranges , working with worksheets is another area you should know about using VBA efficiently in Excel.

Just like any object in VBA, worksheets have different properties and methods associated with them that you can use while automating your work with VBA in Excel.

In this tutorial, I will cover ‘Worksheets’ in detail and also show you some practical examples.

So let’s get started.

If you’re interested in learning VBA the easy way, check out my Online Excel VBA Training .

This Tutorial Covers:

Difference between Worksheets and Sheets in VBA

In VBA, you have two collections that can be a bit confusing at times.

In a workbook, you can have worksheets as well as chart sheets. The example below has three worksheets and one chart sheet.

In Excel VBA:

The ‘Worksheets’ collection refers to the collection of all the worksheet objects in a workbook. In the above example, the Worksheets collection would consist of three worksheets.
The ‘Sheets’ collection would refer to all the worksheets as well as chart sheets in the workbook. In the above example, it would have four elements – 3 Worksheets + 1 Chart sheet.

If you have a workbook that only has worksheets and no chart sheets, then ‘Worksheets’ and ‘Sheets’ collection is the same.

But when you have one or more chart sheets, the ‘Sheets’ collection would be bigger than the ‘Worksheets’ collection

Sheets = Worksheets + Chart Sheets

Now with this distinction, I recommend being as specific as possible when writing a VBA code.

So, if you have to refer to worksheets only, use the ‘Worksheets’ collection, and if you have to refer to all sheets (including chart sheets), the use the ‘Sheets’ collection.

In this tutorial, I will be using the ‘Worksheets’ collection only.

Referencing a Worksheet in VBA

There are many different ways you can use to refer to a worksheet in VBA.

Understanding how to refer to worksheets would help you write better code, especially when you’re using loops in your VBA code.

Using the Worksheet Name

The easiest way to refer to a worksheet is to use its name.

For example, suppose you have a workbook with three worksheets – Sheet 1, Sheet 2, Sheet 3.

And you want to activate Sheet 2.

The above code asks VBA to refer to Sheet2 in the Worksheets collection and activate it.

Since we are using the exact sheet name, you can also use the Sheets collection here. So the below code would also do that same thing.

Using the Index Number

While using the sheet name is an easy way to refer to a worksheet, sometimes, you may not know the exact name of the worksheet.

For example, if you’re using a VBA code to add a new worksheet to the workbook, and you don’t know how many worksheets are already there, you would not know the name of the new worksheet.

In this case, you can use the index number of the worksheets.

Suppose you have the following sheets in a workbook:

The below code would activate Sheet2:

Note that we have used index number 2 in Worksheets(2) . This would refer to the second object in the collection of the worksheets.

Now, what happens when you use 3 as the index number?

It will select Sheet3.

If you’re wondering why it selected Sheet3, as it’s clearly the fourth object.

This happens because a chart sheet is not a part of the worksheets collection.

On the contrary, if you’re using Sheets, Sheets(1) would refer to Sheets1, Sheets(2) would refer to Sheet2, Sheets(3) would refer to Chart1 and Sheets(4) would refer to Sheet3.

This technique of using index number is useful when you want to loop through all the worksheets in a workbook. You can count the number of worksheets and then loop through these using this count (we will see how to do this later in this tutorial).

Note: The index number goes from left to right. So if you shift Sheet2 to the left of Sheet1, then Worksheets(1) would refer to Sheet2.

Using the Worksheet Code Name

One of the drawbacks of using the sheet name (as we saw in the section above) is that a user can change it.

And if the sheet name has been changed, your code wouldn’t work until you change the name of the worksheet in the VBA code as well.

To tackle this problem, you can use the code name of the worksheet (instead of the regular name that we have been using so far).

A code name can be assigned in the VB Editor and doesn’t change when you change the name of the sheet from the worksheet area.

To give your worksheet a code name, follow the below steps:

Click the Developer tab.
Click the View option in the menu and click on Project Window. This will make the Properties pane visible. If the Properties pane is already visible, skip this step.
Click on the sheet name in the project explorer that you want to rename.

The above steps would change the name of your Worksheet in the VBA backend.

In the Excel worksheet view, you can name the worksheet whatever you want, but in the backend, it will respond to both the names – the sheet name and the code name.

In the above image, the sheet name is ‘SheetName’ and the code name is ‘CodeName’. Even if you change the sheet name on the worksheet, the code name still remains the same.

Now, you can use either the Worksheets collection to refer to the worksheet or use the codename.

For example, both the line will activate the worksheet.

The difference in these two is that if you change the name of the worksheet, the first one wouldn’t work. But the second line would continue to work even with the changed name. The second line (using the CodeName) is also shorter and easier to use.

Referring to a Worksheet in a Different Workbook

If you want to refer to a worksheet in a different workbook, that workbook needs to be open while the code runs, and you need to specify the name of the workbook and the worksheet that you want to refer to.

For example, if you have a workbook with the name Examples and you want to activate Sheet1 in the Example workbook, you need to use the below code:

Note that if the workbook has been saved, you need to use the file name along with the extension. If you’re not sure what name to use, take help from Project Explorer.

In case the workbook has not been saved, you don’t need to use the file extension.

Adding a Worksheet

The below code would add a worksheet (as the first worksheet – i.e., as the leftmost sheet in the sheet tab).

It takes the default name Sheet2 (or any other number based on how many sheets are already there).

If you want a worksheet to be added before a specific worksheet (say Sheet2), then you can use the below code.

The above code tells VBA to add a sheet and then uses the ‘Before’ statement to specify the worksheet before which the new worksheet should to be inserted.

Similarly, you can also add a sheet after a worksheet (say Sheet2), using the below code:

If you want the new sheet to be added to the end of the sheets, you need to first know how many sheets are there. The following code first counts the number of sheets , and the adds the new sheet after the last sheet (to which we refer using the index number).

Deleting a Worksheet

The below code would delete the active sheet from the workbook.

The above code would show a warning prompt before deleting the worksheet .

If you don’t want to see the warning prompt, use the below code:

When Application.DisplayAlerts is set to False, it will not show you the warning prompt. If you use it, remember to set it back to True at the end of the code.

Remember that you can’t undo this delete, so use the above code when you’re absolutely sure.

If you want to delete a specific sheet, you can do that using the following code:

You can also use the code name of the sheet to delete it.

Renaming the Worksheets

You can modify the name property of the Worksheet to change its name .

The following code will change the name of Sheet1 to ‘Summary’.

You can combine this with the adding sheet method to have a set of sheets with specific names.

For example, if you want to insert four sheets with the name 2018 Q1, 2018 Q2, 2018 Q3, and 2018 Q4, you can use the below code.

In the above code, we first count the number of sheets and then use a For Next loop to insert new sheets at the end. As the sheet is added, the code also renames it.

Assigning Worksheet Object to a Variable

When working with worksheets, you can assign a worksheet to an object variable, and then use the variable instead of the worksheet references.

For example, if you want to add a year prefix to all the worksheets, instead of counting the sheets and the running the loop that many numbers of times, you can use the object variable.

Here is the code that will add 2018 as a prefix to all the worksheet’s names.

The above code declares a variable Ws as the worksheet type (using the line ‘Dim Ws As Worksheet’).

Now, we don’t need to count the number of sheets to loop through these. Instead, we can use ‘For each Ws in Worksheets’ loop. This will allow us to go through all the sheets in the worksheets collection. It doesn’t matter whether there are 2 sheets or 20 sheets.

While the above code allows us to loop through all the sheets, you can also assign a specific sheet to a variable.

In the below code, we assign the variable Ws to Sheet2 and use it to access all of Sheet2’s properties.

Once you set a worksheet reference to an object variable (using the SET statement), that object can be used instead of the worksheet reference. This can be helpful when you have a long complicated code and you want to change the reference. Instead of making the change everywhere, you can simply make the change in the SET statement.

Note that the code declares the Ws object as the Worksheet type variable (using the line Dim Ws as Worksheet).

Hide Worksheets Using VBA (Hidden + Very Hidden)

Hiding and Unhiding worksheets in Excel is a straightforward task.

You can hide a worksheet and the user would not see it when he/she opens the workbook. However, they can easily unhide the worksheet by right-clicking on any sheet tab.

But what if you don’t want them to be able to unhide the worksheet(s).

You can do this using VBA.

The code below would hide all the worksheets in the workbook (except the active sheet), such that you can not unhide it by right-clicking on the sheet name.

In the above code, the Ws.Visible property is changed to xlSheetVeryHidden .

When the Visible property is set to xlSheetVisible, the sheet is visible in the worksheet area (as worksheet tabs).
When the Visible property is set to xlSheetHidden, the sheet is hidden but the user can unhide it by right-clicking on any sheet tab.
When the Visible property is set to xlSheetVeryHidden, the sheet is hidden and cannot be unhidden from worksheet area. You need to use a VBA code or the properties window to unhide it.

If you want to simply hide sheets, that can be unhidden easily, use the below code:

The below code would unhide all the worksheets (both hidden and very hidden).

Hide Sheets Based on the Text in it

Suppose you have multiple sheets with the names of different departments or years, and you want to hide all the sheets except the ones that have the year 2018 in them.

You can do this using a VBA INSTR function .

The code below would hide all the sheets except the ones with the text 2018 in them.

In the above code, the INSTR function returns the position of the character where it finds the matching string. If it doesn’t find the matching string, it returns 0.

The above code checks whether the name has the text 2018 in it. If it does, nothing happens, else the worksheet is hidden.

You can take this a step further by having the text in a cell and using that cell in the code.

This will allow you to have a value in the cell, and then when you run the macro, all the sheets, except the one with the matching text in it, will remain visible (along with the sheets where you’re entering the value in the cell).

Sorting the Worksheets in an Alphabetical Order

Using VBA, you can quickly sort the worksheets based on their names.

For example, if you have a workbook that has sheets for different departments or years, then you can use the below code to quickly sort these sheets in an ascending order.

Note that this code works well with text names and in most of the cases with years and numbers too. But it can give you the wrong results in case you have the sheet names as 1,2,11. It will sort and give you the sequence 1, 11, 2. This is because it does the comparison as text and considers 2 bigger than 11.

Protect/Unprotect All the Sheets at One Go

If you have a lot of worksheets in a workbook and you want to protect all the sheets, you can use the VBA code below.

It allows you to specify the password within the code. You will need this password to unprotect the worksheet.

The following code would unprotect all the sheets in one go.

Creating a Table of Contents of All Worksheets (with Hyperlinks)

If you have a set of worksheets in the workbook and you want to quickly insert a summary sheet which has the links to all the sheets, you can use the below code.

The above code inserts a new worksheet and names it Index.

It then loops through all the worksheets and creates a hyperlink for all the worksheets in the Index sheet.

Where to Put the VBA Code

Wondering where the VBA code goes in your Excel workbook?

Excel has a VBA backend called the VBA editor. You need to copy and paste the code into the VB Editor module code window.

Here are the steps to do this:

In the Project Explorer pane in the VB Editor, right-click on any object for the workbook in which you want to insert the code. If you don’t see the Project Explorer go to the View tab and click on Project Explorer.

You May Also Like the Following Excel VBA Tutorials:

Working with Workbooks using VBA .
Using IF Then Else Statements in VBA .
For Next Loop in VBA .
Creating a User-Defined Function in Excel .
How to Record a Macro in Excel .
How to Run a Macro in Excel .
Excel VBA Events – An Easy (and Complete) Guide .
How to Create an Add-in in Excel .
How to Save and Reuse Macro using Excel Personal Macro Workbook .
Using Active Cell in VBA in Excel (Examples)
How to Open Excel Files Using VBA (Examples)

FREE EXCEL BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

3 thoughts on “working with worksheets using excel vba (explained with examples)”.

How do you refer to the sheet code name of a different workbook? in particular, I would like to set a work book as a variable (set wb = workbook) and then refer to this work book’s sheets using their code names in case individuals decide to change the sheet name.

Hi I’ve created just one worksheet where a number of the columns have drop down lists. I wanted to able to have multiple selection non repetitive on these so I adapted the VBA code as indicated in one of your earlier tutorials. This only worked for a limited number of rows/ columns though and I need it to apply to the whole worksheet. Is there a code that satisfies this??? Is it the code above??? Where do I insert it in existing code?? Your help would be really appreciated

Making the same change to multiple worksheets or say i want to have one master worksheet and the changes i make in master sheet will automatically implement in all other (NOTE : without selecting all worksheets using Ctrl Key)

Passing a Worksheet into a Sub()

Thread starter Chrisjd2
Start date Feb 16, 2017

Board Regular

Feb 16, 2017

Excel Facts

MrExcel MVP, Moderator

Re: Passing a Worksheet into a Sub(), HELP! You don't really need the additional variable, just replace this: Code: SHT = wsOld Call LoadDataString(SHT) with this: Code: Call LoadDataString(wsOld)

Re: Passing a Worksheet into a Sub(), HELP! I want to be able to run the macro, with applying it to different sheets instead of re-writing the macro each time. I.e Code: Sub RunProgram SHT =wsOld Call LoadDataString(SHT) SHT =wsNew Call LoadDataString(SHT) End sub

MrExcel MVP, Junior Admin

Re: Passing a Worksheet into a Sub(), HELP! Since SHT is a worksheet object, shouldn't it be: Code: [COLOR=#ff0000][B]Set[/B][/COLOR] SHT = wsOld Though Rory makes a good point. There isn't really any reason to have this extra variable. It seems rather unnecessary. Just use: Code: Call LoadDataString(wsOld) Call LoadDataString(wsNew)

Joe4 said: Since SHT is a worksheet object, shouldn't it be: Code: [COLOR=#ff0000][B]Set[/B][/COLOR] SHT = wsOld Though Rory makes a good point. There isn't really any reason to have this extra variable. It seems rather unnecessary. Just use: Code: Call LoadDataString(wsOld) Call LoadDataString(wsNew) Click to expand...

Re: Passing a Worksheet into a Sub(), HELP! It will simply work as it is. As far as the LoadDataString routine is concerned, the variable passed in is called SHT. That does not require you to pass in a variable of the same name.

Re: Passing a Worksheet into a Sub(), HELP! Nice one thanks, It works well now. How could I do the same with the Getopenfilename? So the Title is different etc... Code: Sub CallRoutine() Set wsOld = Worksheets("Old Variant CADEX") Set wsNew = Worksheets("New Variant CADEX") Dim Title As String Call LoadData(wsOld, "Text Files (*.txt),*.txt", , "Old Data Drop") End Sub Sub LoadCadex(Worksheet, Title As String) Dim Myfile As String Dim myline As String Dim Fileline() As String Myfile = Application.GetOpenFilename() If Myfile = "" Then GoTo LINE2 End If Counter = 1

Re: Passing a Worksheet into a Sub(), HELP! Just pass the argument along: Code: Myfile = Application.GetOpenFilename(Title:=Title) You can probably see why it's better to use a variable name that isn't also the name of a parameter.

Re: Passing a Worksheet into a Sub(), HELP! I see. Code: Sub CallRoutine() Set wsOld = Worksheets("Old Variant CADEX") Set wsNewLocations = Worksheets("New Cadex Locations") Set wsOldLocations = Worksheets("Old Cadex Locations") Dim Title As String myfile = application.GetOpenFilename("Text Files (*.txt),*.txt", , "Old Data") Call LoadData(wsOld, myfile) End Sub Private Sub LoadData(Worksheet, myfile As String) Dim myfile As String Dim myline As String Dim Fileline() As String 'myfile = Application.GETOPENFILENAME If myfile = "" Then GoTo LINE2 End If I get an error, Byref argument type mismatch.. P.s I am new to VBA and self taught lol

Re: Passing a Worksheet into a Sub(), HELP! Don't know if it is what is causing your problem, but you should never use reserved words (the names of functions, objects, properties, methods, etc) as the name of your Procedures, Functions, or Variables. It can cause ambiguity, unexpected results, and errors. You have a variable named Worksheet, which is a reserved word: Code: Private Sub LoadData([COLOR=#ff0000]Worksheet[/COLOR], myfile As String) Use something different, like "myWorksheet" or "ws".

Similar threads

Mar 2, 2023

ChrisGudrich
Sep 5, 2022
May 31, 2023
HeinrichPaul
Sep 17, 2023
May 14, 2023

Forum statistics

Share this page.

We've detected that you are using an adblocker.

Which adblocker are you using.

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock

Excel VBA protect worksheet with password

This Excel VBA tutorial explains how to use VBA to protect worksheet with password.

You may also want to read:

Excel VBA hide worksheet with password (xlVeryHidden)

Excel VBA Add password to Excel workbook in folder

Protect VBA code with password

Excel VBA copy contents of protected worksheet

Different kinds of workbook protection

There are several kinds of protection in Excel.

Add password to workbook . Users are asked to open the Excel file with password.
Protect workbook with password . Protect the structure of all worksheets (do not allow insert, delete, rename, move, copy, hide or unhide worksheets)
Hide worksheet with password . Hide the worksheet and add a password to unhide the worksheet.
Protect worksheet (cells) with password. Restrict users to edit the cell

Lock Cell and Unlock Cell

Before doing any worksheet Protection, the first thing to do is to ensure the Cell you want to protect is locked.

Right click on a Cell > Format Cells > Protection > check Locked

To lock a Cell in VBA

To unlock a Cell in VBA

Similarly, Hidden can also be enabled in VBA

Protect worksheet

Now the next step is to do the actual protection using Worksheet.Protect Method .

Syntax of Worksheet.Protect

The simplest way to set password is to leave everything default and just set the password.

To protect all worksheets in workbook

Since everyone can press ALT+F11 to see your password, you should lock the project view of VBE.

Press ALT+F11 > Navigate to Tools > VBAProject Properties > Protection > check the box “Lock project for viewing” > enter password (close the workbook and reopen to take effect)

Unprotect worksheet

Unprotect is more simple, unprotect Method has only one optional argument – the password.

If the sheet or workbook isn’t protected with a password, this argument is ignored.

If you omit this argument for a sheet that’s protected with a password, you’ll be prompted for the password.

If you omit this argument for a workbook that’s protected with a password, the method fails.

If you are trying to modify a protected worksheet, you should run Macro to unprotect > modify > protect

Unprotect worksheet > do something > protect worksheet

Because you cannot manipulate a worksheet while it is protected, when you write a Macro for a protected worksheet, it is best to unprotect a protected worksheet, then run a Macro, finally protect it back.

The Complete Guide To The VBA Worksheet

by Paul Kelly | | Excel Specific , VBA Language | 55 comments

“The visionary starts with a clean sheet of paper, and re-imagines the world” – Malcolm Gladwell

This post provides a complete guide to using the Excel VBA Worksheet in Excel VBA. If you want to know how to do something quickly then check out the quick guide to the VBA Worksheet below.

If you are new to VBA then this post is a great place to start. I like to break things down into simple terms and explain them in plain English without the jargon.

You can read through the post from start to finish as it is written in a logical order. If you prefer, you can use the table of contents below and go directly to the topic of your choice.

1 A Quick Guide to the VBA Worksheet
2 Introduction
3.1 Hide Worksheet
3.2 Protect Worksheet
3.3 Subscript Out of Range
4 Using the Index to Access the Worksheet
5.1 Code Name in other Workbooks
5.2 Code Name Summary
6 The Active Sheet
7 Declaring a Worksheet Object
8 Accessing the Worksheet in a Nutshell
9 Add Worksheet
10 Delete Worksheet
11 Loop Through the Worksheets
12 Using the Sheets Collection
13 Conclusion
14 What’s Next?

A Quick Guide to the VBA Worksheet

The following table gives a quick run down to the different worksheet methods.

Note: I use Worksheets in the table below without specifying the workbook i.e. Worksheets rather than ThisWorkbook.Worksheets , wk.Worksheets etc. This is to make the examples clear and easy to read. You should always specify the workbook when using Worksheets . Otherwise the active workbook will be used by default.

Introduction

The three most important elements of VBA are the Workbook , the Worksheet and Cells . Of all the code your write, 90% will involve one or all of them.

The most common use of the worksheet in VBA is for accessing its cells. You may use it to protect, hide, add, move or copy a worksheet. However, you will mainly use it to perform some action on one or more cells on the worksheet.

Using Worksheets is more straightforward than using workbooks. With workbooks you may need to open them, find which folder they are in, check if they are in use and so on. With a worksheet, it either exists in the workbook or it doesn’t.

Accessing the Worksheet

In VBA, each workbook has a collection of worksheets. There is an entry in this collection for each worksheet in the workbook. This collection is simply called Worksheets and is used in a very similar way to the Workbooks collection. To get access to a worksheet all you have to do is supply the name.

The code below writes “Hello World” in Cell A1 of Sheet1, Sheet2 and Sheet3 of the current workbook.

The Worksheets collection is always belong to a workbook. If we don’t specify the workbook then the active workbook is used by default.

Hide Worksheet

The following examples show how to hide and unhide a worksheet

If you want to prevent a user accessing the worksheet, you can make it “very hidden”. This means it can only be made visible by the code.

Protect Worksheet

Another example of using the worksheet is when you want to protect it

Subscript Out of Range

When you use Worksheets you may get the error:

Run-time Error 9 Subscript out of Range

This means you tried to access a worksheet that doesn’t exist. This may happen for the following reasons

The worksheet name given to Worksheets is spelled incorrectly.
The name of the worksheet has changed.
The worksheet was deleted.
The index was to large e.g. You used Worksheets(5) but there are only four worksheets
The wrong workbook is being used e.g. Workbooks( “book1.xlsx” ).Worksheets(“Sheet1”) instead of Workbooks( “book3.xlsx” ).Worksheets(“Sheet1”) .

If you still have issues then use one of the loops from Loop Through The Worksheets section to print the names of all worksheets the collection.

Using the Index to Access the Worksheet

So far we have been using the sheet name to access the sheet. The index refers to the sheet tab position in the workbook. As the position can easily be changed by the user it is not a good idea to use this.

The following code shows examples of using the index

In the example above, I used Debug.Print to print to the Immediate Window. To view this window select View->Immediate Window(or Ctrl G)

Using the Code Name of a Worksheet

The best method of accessing the worksheet is using the code name. Each worksheet has a sheet name and a code name. The sheet name is the name that appears in the worksheet tab in Excel.

Changing the sheet name does not change the code name meaning that referencing a sheet by the code name is a good idea.

If you look in the VBE property window you will see both names. In the image you can see that the code name is the name outside the parenthesis and the sheet name is in the parenthesis.

If your code refers to the code name then the user can change the name of the sheet and it will not affect your code. In the example below we reference the worksheet directly using the code name.

This makes the code easy to read and safe from the user changing the sheet name.

Code Name in other Workbooks

There is one drawback to using the code name. It can only refer to worksheets in the workbook that contains the code i.e. ThisWorkbook.

However, we can use a simple function to find the code name of a worksheet in a different workbook.

Using the above code means that if the user changes the name of the worksheet then your code will not be affected.

There is another way of getting the sheet name of an external workbook using the code name. You can use the VBProject element of that Workbook.

You can see how to do this in the example below. I have included this for completeness only and I would recommend using the method in the previous example rather than this one.

Code Name Summary

The following is a quick summary of using the Code Name

The code name of the worksheet can be used directly in the code e.g. Sheet1.Range
The code name will still work if the worksheet name is changed.
The code name can only be used for worksheets in the same workbook as the code.
Anywhere you see ThisWorkbook.Worksheets(“sheetname”) you can replace it with the code name of the worksheet.
You can use the SheetFromCodeName function from above to get the code name of worksheets in other workbooks.

The Active Sheet

The ActiveSheet object refers to the worksheet that is currently active. You should only use ActiveSheet if you have a specific need to refer to the worksheet that is active.

Otherwise you should specify the worksheet you are using.

If you use a worksheet method like Range and don’t mention the worksheet, it will use the active worksheet by default.

Declaring a Worksheet Object

Declaring a worksheet object is useful for making your code neater and easier to read.

The next example shows code for updating ranges of cells. The first Sub does not declare a worksheet object. The second sub declares a worksheet object and the code is therefore much clearer.

You could also use the With keyword with the worksheet object as the next example shows.

Accessing the Worksheet in a Nutshell

With all the different ways to access a worksheet, you may be feeling overwhelmed or confused. So in this section, I am going to break it down into simple terms

1. If you want to use whichever worksheet is currently active then use ActiveSheet .

2. If the worksheet is in the same workbook as the code then use the Code Name .

3. If the worksheet is in a different workbook then first get workbook and then get the worksheet.

If you want to protect against the user changing the sheet name then use the SheetFromCodeName function from the Code Name section.

Add Worksheet

The examples in this section show you how to add a new worksheet to a workbook. If you do not supply any arguments to the Add function then the new worksheet will be placed before the active worksheet.

When you add a Worksheet, it is created with a default name like “Sheet4”. If you want to change the name then you can easily do this using the Name property.

The following example adds a new worksheet and changes the name to “Accounts”. If a worksheet with the name “Accounts” already exists then you will get an error.

In the previous example, you are adding worksheets in relation to the active worksheet. You can also specify the exact position to place the worksheet.

To do this you need to specify which worksheet the new one should be inserted before or after. The following code shows you how to do this.

Delete Worksheet

To delete a worksheet you simply call the Delete member.

Excel will display a warning message when you delete a worksheet. If you want to hide this message you can use the code below

There are two issues to watch out for when it comes to deleting worksheets.

If you try to access the worksheet after deleting it you will get the “Subscript out of Range” error we saw in the Accessing the Worksheet section.

The second issue is when you assign a worksheet variable. If you try to use this variable after the worksheet is deleted then you will get an Automation error like this

Run-Time error -21147221080 (800401a8′) Automation Error

If you are using the Code Name of the worksheet rather than a variable, then this will cause Excel to crash rather than give the automation error.

The following example shows how an automation errors occurs

If you assign the Worksheet variable to a valid worksheet it will work fine

Loop Through the Worksheets

The Worksheets member of Workbooks is a collection of worksheets belonging to a workbook. You can go through each sheet in the worksheets collection using a For Each Loop or a For Loop.

The following example uses a For Each loop.

The next example uses the standard For loop

You have seen how to access all open workbooks and how to access all worksheets in ThisWorkbook. Lets take it one step further. Lets access all worksheets in all open workbooks.

Note: If you use code like this to write to worksheets then back everything up first as you could end up writing the incorrect data to all the sheets.

Using the Sheets Collection

The workbook has another collection similar to Worksheets called Sheets . This causes confusion at times among users. To explain this first you need to know about a sheet type that is a chart.

It is possible in Excel to have a sheet that is a chart. To do this

Create a chart on any sheet.
Right click on the chart and select Move.
Select the first option which is “New Sheet” and click Ok.

Now you have a workbook with sheets of type worksheet and one of type chart.

The Worksheets collection refers to all worksheets in a workbook. It does not include sheets of type chart.
The Sheets collection refers to all sheets belonging to a workbook including sheets of type chart.

There are two code examples below. The first goes through all the Sheets in a workbook and prints the name of the sheet and type of sheet it is. The second example does the same with the Worksheets collection.

To try out these examples you should add a Chart sheet to your workbook first so you will see the difference.

If do not have chart sheets then using the Sheets collection is the same as using the Worksheets collection.

This concludes the post on the VBA Worksheet. I hope you found it useful.

The three most important elements of Excel VBA are Workbooks , Worksheets and Ranges and Cells . These elements will be used in almost everything you do. Understanding them will make you life much easier and make learning VBA much simpler.

What’s Next?

Free VBA Tutorial If you are new to VBA or you want to sharpen your existing VBA skills then why not try out the The Ultimate VBA Tutorial .

Related Training: Get full access to the Excel VBA training webinars and all the tutorials .

( NOTE: Planning to build or manage a VBA Application? Learn how to build 10 Excel VBA applications from scratch.)

55 Comments

Thank you for the excellent information for a VBA novice of some 10 years

You’re welcome Peter.

I’ve just come across your blog and it looks most informative. I’ve not had time to read through it yet and the answer to my question may be in there somewhere but in the meantime Is there a shorter way (in a macro) to write If Cell.Value = “A” Or Cell.Value = “B” Or Cell.Value = “C” Then Cell.Value = 2

Many thanks

You can use the Select Case statement to do it

Thanks for that Paul.

In the time between my query and your answer I did find a way except a bit longer than what you have suggested.

This is what I found

For Each oCell In Range(“B11:M32”) Select Case oCell.Value Case Is = “A”, “B”, “C”: oCell.Value = 2 Case Is = “D”, “E”, “F”: oCell.Value = 3 Case Is = “G”, “H”, “I”: oCell.Value = 4 Case Is = “J”, “K”, “L”: oCell.Value = 5 Case Is = “M”, “N”, “O”: oCell.Value = 6 Case Is = “P”, “Q”, “R”, “S”: oCell.Value = 7 Case Is = “T”, “U”, “V”: oCell.Value = 8 Case Is = “W”, “X”, “Y”, “Z”: oCell.Value = 9 End Select Next

Anyway thanks again. Much appreciated.

How to change one or moreTabs name or worksheets name in upper Case by vba? my worksheets has 100 nos

I use the code name to identify the different types of worksheets that are being created/used; allowing users to rename the worksheet as they see fit. From your experience what is the best way to set the code name ?

I normally set it manually in the property windows. It can be done using the code but normally this is not necessary.

Thanks for sharing !!

Hi Paul, I am really really really thankful for your very informative, didactic and amusing posts. I have learned a lot since I began to read and rewrite your code, while adding slight changes and experimenting. I just have a comment, I found that into the Sub AllSheetNames(), the second “next” asks for an “i” variable where the proper one should be “wrk” Thank you again for everything and keep making live videos.

Thanks Jorge, I have update the code. Glad you enjoy the website posts.

Thanks for your great effort Paul Kelly. It’s very helpful to developing towards VBA programmer.

But one question, you have not shown how the “codename” for the worksheet is made. Only “codename” is displayed in the property screen shot.

Thanks for sharing knowledge…. I am glad i found this site.

Ya i got it, its already mentioned there. Sorry i missed it.

No problem Afzal.

Hallo Paul, your whole site is the mandatory start lane and pit stop for every vba writer. I get confused by example “Code Name in other Workbooks”: I miss somewhere something like

Dim iWB As Workbook For Each iWB In Application.Workbooks ‘Loop thru Worksheets and check condition Next

Thank again and kind regards

You stated that codenames can only be used in “Thisworkbook”, but in my testing the module that I’m running writes to two workbooks that I have opened at the same if the other workbook is active. Here’s what I did. I declared a public codename in workbook1 and set it in ThisWorkbook object using Auto_Open. (I’ve tested setting it in a module as well and I get the same results) The module which contains my code starts with “With ThisWorkbook” before calling the public variable. As a test, I renamed a sheet from each workbook with the same codename. I would expect that my code would only run in workbook1 as the public variable is written, set and called in workbook1; on top of that I declared “With Thisworkbook” at the start of my module.

Do you know how I can prevent the module from running in the wrong workbook? I know I can simply rename the codename in workbook1 to something highly unlikely, but for theoretical purposes I’d like not to do that.

Thanks, Chris

Upon further testing regardless of the other workbooks codename if it was active my module would write to it. I changed the codenames to differ and I had the same results; even if the other workbook was an xlsx file the module still wrote to it. For now, I’m going with a workaround to state in my code Thisworkbook.active. This prevented writing to the other workbook even if I clicked on it during runtime.

Okay, so what I found out is that in my module I have lines of code such as range(“A2”).value, etc. As a result, excel or vba reads it like ActiveSheet.Range(“A2”).value. I had thought that starting the code with “With Thisworkbook” I would have avoided the code from writing or reading other workbooks.

What I did to resolve this is instead of range(“A2”).value and I used VBsht.Range(“A2”).value with VBsht being my public variable or object.

I am new to VBA and am learning from beginning. Thank you for your useful sharing!

You’re welcome.

OMG, I have been looking for someone to teach me macros as how to write macros. I found this site, it seems to me a new world has opened its door to me. It is unbelievable!!!

Glad you like it so much:-)

Hello Paul,

Great stuff you have here. I really appreciate your thoroughness and clarity. For a nitpicker like me, it is great to have clear explanations for many things you write in code.

In this article, there is a section called “Code Name in other Workbooks” which I have a question about. You write, “There is another way of getting the sheet name of an external workbook using the code name. You can use the VBProject element of that Workbook… I would recommend using the method in the previous example rather than this one”. Could you explain why you recommend the looping technique over using VBProject elements?

Using the VBProject requires that the user has checked the “Trust Access” box in the security settings. Which is fine if you are just using it yourself.

I don’t know how I stumbled upon your site, but I am confident that this will hands down be the greatest thing in my VBA journey.

I believe in the sub UseSheet of ‘Code Name in other Workbooks’ section, the argument ThisWorkbook must be replaced with the workbook you are trying to access the sheets of, like Workbooks(“Book2.xlsx”) .

Thanks Loki. ThisWorkbook refers to the current workbook i.e. the workbook containing the VBA code.

Dear Paul, Thanks for this informative article. I tried to set two worksheetsheet variables to access two sheets of a new workbook but VBA is throwing error on assigning second sheet to second variable. Below is the code

Sub test27() Dim temp_wkb As Workbook Dim temp_sheet1, temp_sheet2 As Sheets Set temp_wkb = Workbooks.Add Set temp_sheet1 = temp_wkb.Sheets(1) Set temp_sheet2 = temp_wkb.Sheets(2) End Sub

I get Error 13 type mismatch on “Set temp_sheet2 = temp_wkb.Sheets(2)”

Could you please guide as to what am I doing wrong here?

Regards Ashish

temp_sheet2 As Sheets

This code should be “As Sheet” or better still “As Worksheet”

Why this Sub procedure doesn’t work for me. Please answer. Public Sub UseCodeName2()

‘ Using the code name of the worksheet Debug.Print CodeName.Name CodeName.Range(“A1”) = 45 CodeName.Visible = True

Goodby ! Dušan

You must have a worksheet with the code name set to CodeName

Paul, I read all the articles on your site and I would like to show you all my gratitude for your information sharing. Having a simple structure with multiple examples, easy to copy and to understand, makes the student’s job very simple. You are an excellent teacher.

Thank you for the nice work.

Thanks Jérôme

hi, is it possible to refer with a variable to the sheets codename;

dim sht as worksheet, strCName as string strCName = “cnTestCodeName” set sht = sheets(strCName) ‘this does not fly for me when i want to refer to the sheets CodeName

in general impressive tutorials / on udemy it could had gone some deeper for me, but good.

Why would you want to? When you use the codename you are directly accessing the worksheet object. You can do this: Dim sht As Worksheet Set sht = sheets(cnSheet1.Name)

But it’s the same as Set sht = cnSheet1

Thanks for reply, will try to explain my ‘idea’. I have a hidden ‘classSheet’ that holds a range of predefined zoom values for a set of sheets in the workbook. A function setting all those predefined zooms to the ‘default’ by looping true all sheets referred to by codename. Now if I use the sheetname instead the user could have changed this sheetname. For the codename I wanted to check if the sheet still exists (Excel will crash referring to a sheets codename that does not exist) and loop true the collection. As a result, of this approach, I thought to be pretty sure all goes as intended to. It is probably not a capital case but I thought I miss out on something easy once you know. Thanks.

You can use the SheetFromCodeName() function in this section of the post to get the worksheet object from the codename text.

sorry for late reply, i only have time in my weekends. thanks for the tip, prefect workable approach. Ludovic

If I found you earlier, I could dig treasure in Excel. Until now, I just have used simple function of it. Anyway, I keep turning on your tutorial everyday. Again, Thanks for your great lecture.

Thanks Shin.

Sir, I have a workbook in which there is a worksheet in which we had used some formulas. I want to make a duplicate sheet by a command button with only values (paste special). Please Guide me

Hi Paul and all,

Thanks for the great resources herein.

I would like to copy the values in cell C1 from a number of worksheets and paste in range C2 : C13 of my summary worksheet in the same workbook, and subsequently in the empty cells that follow. But instead of getting a set of 12 unique values, I get a long list with the same values from one worksheet.

Here is my code: Sub Tabulates()

Dim erow As Long

For i = 1 To Worksheets.Count If Worksheets(i).Name “Data” Then

erow = Sheet1.Cells(Sheet1.Rows.Count, 1).End(xlUp).Offset(1, 0).row + 1

Worksheets(i).Range(“C1”).Copy Sheet1.Range((“C2:C13”) & erow).Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True Application.CutCopyMode = False

End If Next End Sub

Kindly assist. Thank you.

Can you please help me to solve this problem? Eight number line shows error. Sub Macro1() Dim c, m, n, f As Integer c = 4 ‘Total room number m = 1 For f = 1 To c If (f <= c – 3) Then Worksheets("Bedroom" & m).Activate Worksheets("Load Factor").Cells(6, f + 2).Formula = "="Bedroom"&m!B2" m = m + 1 End If Next f End Sub

What is the error?

Dear Sir, I am looking for a vba code to find whether a worksheet is “Protected” or “Unprotected”. Thanks & Kind Regards Majeed

Hi, Great resources, specially for an absolute beginner like me.

I almost know nothing about Excel, but I’m really motivated to learn.

I’m on a project to collect software inventory from a list of computers, for this part I have managed to make individual inventory and merge the different worksheets in a single workbook.

In my workbook I also have the content of the base image, which I use as a reference to remove the lines that are in this specific sheet from the other sheets, that’s working fine.

But as it sometimes happens That on certain computers, there is no other software than the base image I get empty inventory sheets after the cleanup and that’s causing me trouble as sometimes my account doesn’t have access to certain computers and instead of crashing my entire tool, I issue an empty sheet and that’s my way to find computers on which I have no access.

But after cleaning up my sheets I can’t figure out which are empty because the lack of access or the absence of software added to the base image.

So, here’s where my problem begins, I just can’t figure out how to instead of deleting those lines, just change the background colors for these rows.

So here is my actual code and any help would be really appreciated.

******************************** Option Explicit

Sub NettoyeurDeFeuilles() Application.ScreenUpdating = False

Dim ws As Worksheet Dim startRow As Integer

startRow = 1 Dim row As Integer

row = startRow

Dim bRow As Integer

For Each ws In ThisWorkbook.Worksheets With ws ‘Ne pas chercher dans la feuille Image_De_Base_W7! If ws.Name = “Image_De_Base_W7” Then GoTo myNext

Do While (Worksheets(ws.Name).Range(“A” & row).Value “”)

Dim aVal As String

aVal = Worksheets(ws.Name).Range(“A” & row).Value

bRow = startRow

Do While (Worksheets(“Image_De_Base_W7”).Range(“A” & bRow).Value “”)

Dim aVal2 As String

aVal2 = Worksheets(“Image_De_Base_W7”).Range(“A” & bRow).Value

If (aVal = aVal2) Then

Worksheets(ws.Name).Rows(row).Delete ‘ Une entrée à supprimé à été trouvé row = row – row

‘ Worksheets(ws.Name).Rows(row).Cells.Interior.ColorIndex = 3

bRow = bRow + 1

row = row + 1 Loop

myNext: End With

***********************

Suppose you have a 7 sheets. I want to protect sheet 2,4,6 by immediate window vba code. how?

Hello Paul Kelly, thanks for the amazing resources on your site, I have been using them a lot and it helped me learn faster and save a lot of time. I have a suggestion for you. I have modified your code to use worksheets froom codenames on another workbook

Here is the code:

‘ https://excelmacromastery.com/ Public Function UseSheetCodeName(CodeName As String, Wbk As Workbook) As Worksheet

Dim sh As Worksheet ‘ Get the worksheet using the codename Set sh = SheetFromCodeName(CodeName, Wbk) ‘ Use the worksheet Debug.Print sh.Name

End Function

‘ This function gets the worksheet object from the Code Name Private Function SheetFromCodeName(Name As String, bk As Workbook) As Worksheet

Dim sh As Worksheet For Each sh In bk.Worksheets If sh.CodeName = Name Then Set SheetFromCodeName = sh Exit For End If Next sh

Hi Paul. I’m new to VBA and am stuck on the ‘Code Name in Other Workbooks’ section. I’ve read the comments and can’t work it out. When I run the code I see the following error:

Run-time error ’91’ Object variable or With block variable not set.

Debug references the line:

Debug.Print sh.Name

Am I supposed to have created a separate workbook and worksheet before running the macro?

Hi Paul, After returning to the code the morning after, I’m pleased to say perseverance has won the day. I declared the ‘name’ variable incorrectly in SheetFromCodeName function line. I declared it as a plural ‘names’. I don’t yet know how to step through the code to debug this sort of thing but will get there. Thanks again.

Glad you got it sorted James.

Hi Sir, I’m new to VBA and am stuck on the Update or delete function. I’ve read the comments and can’t work it out. When I run the code I see the following error:

Run-time error ‘1004’: “Unable to get the Match property of the WorksheetFunction class” Show.

Click on Debug,

Selected_Row = Application.WorksheetFunction.Match(CLng(Me.TxtSalPri.Value), sh.Range(“A:A”), 0)

that line is highlighted with yellow

Code name from other workbooks: I’m a tad lost but see the huge value of understanding this section. 1) When I run this code I get an error: Object varialbe or With block variable not set. I assume this is the “Codename” variable in the function call? 2) Overall, I think the purpose of this is to find all code names in the other workbook, but it passes “ThisWorkbook” to the function and it seems it should be passing the other workbook name. 3) Where does the other workbook name come in to find the sh code name(s)? I assume this is just trying to confirm that a sh code name exists in the other workbook but I can’t resolve the error to continue digesting how the code is working.

It takes a workbook object as the parameter and not the workbook name. I used ThisWorkbook as an example here as it is easy to demonstrate the code. You can replace it with any workbook object.

Hi, Good evening Paul, while going over my correspondence I found your email, which to my surprise have the link to this Worderfull Complete Guide.. Immediately I downloaded as a PDF and found it covers very Important information which I was searching days ago on the Internet.. You are Great.. really Thank You for Sharing..

If God spears life I will try sending a Contribution, unfortunately I can promise much, but a couple of hundreds always help.. Best Regards..

Glad you found it useful.

Submit a Comment Cancel reply

Your email address will not be published. Required fields are marked *

You are not logged in

You are not currently logged in.

This browser is no longer supported.

Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.

Worksheet.Range property (Excel)

8 contributors

Returns a Range object that represents a cell or a range of cells.

expression . Range ( Cell1 , Cell2 )

expression A variable that represents a Worksheet object.

Cell1 and Cell2 can be A1-style references in the language of the macro. The range references can include the range operator (a colon), intersection operator (a space), or union operator (a comma). They can also include dollar signs, which are ignored. A local defined name can be a range reference. If you use a name, the name is assumed to be in the language of the macro.

Cell1 and Cell2 can be Range objects that contain a single cell, column, row, or any other range of cells.

Often, Cell1 and Cell2 are single cells in the upper-left and lower-right corners of the range returned.

When used without an object qualifier, this property is a shortcut for ActiveSheet.Range (it returns a range from the active sheet; if the active sheet isn't a worksheet, the property fails).

When applied to a Range object, the property is relative to the Range object. For example, if the selection is cell C3, Selection.Range("B1") returns cell D3 because it is relative to the Range object returned by the Selection property. On the other hand, the code ActiveSheet.Range("B1") always returns cell B1.

This example sets the value of cell A1 on Sheet1 to 3.14159.

This example creates a formula in cell A1 on Sheet1.

This example loops on cells A1:D10 on Sheet1 of the active workbook. If one of the cells has a value less than 0.001, the code replaces that value with 0 (zero).

This example loops on the range named TestRange and displays the number of empty cells in the range.

This example sets the font style in cells A1:C5 on Sheet1 of the active workbook to italic. The example uses Syntax 2 of the Range property.

This example compares the Worksheet.Range property, the Application.Union method, and the Application.Intersect method.

Support and feedback

Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.

Was this page helpful?

Submit and view feedback for

Additional resources

Power Spreadsheets

Excel and VBA tutorials and training. Learn how to use Microsoft Excel and Visual Basic for Applications now.

Excel VBA Protect Sheet Without Password in 2 Easy Steps (+ Free Easy-To-Adjust Excel Workbook Example)

This Excel VBA Protect Sheet Without Password Tutorial is accompanied by an Excel workbook with the data and VBA code I use when describing the step-by-step process below. Get this example workbook (for free) by clicking the button below .

Get immediate free access to the Excel VBA Protect Sheet Without Password workbook example

The VBA code in the Excel workbook that accompanies this Excel VBA Protect Sheet Without Password Tutorial is (always) stored in the Visual Basic Editor (VBE). If you don't know how to work with the VBE, I suggest you read my Visual Basic Editor (VBE) Tutorial. I link to this Tutorial in the Related Excel Macro and VBA Training Materials and Resources Section below.

Table of Contents

Related Excel Macro and VBA Training Materials and Resources

The following Excel Macro and VBA Tutorials may help you better understand and implement the contents below.

Excel Macros: Click here to open .
Excel VBA: Click here to open .
Enable macros in Excel: Click here to open .
Work with the Visual Basic Editor (VBE): Click here to open .
Create Sub procedures: Click here to open .
Refer to objects ( click here to open ), including sheets ( click here to open ).
Methods: Click here to open .
Data types: Click here to open .
Loops: Click here to open .
Activate workbook: Click here to open .
Create new workbook: Click here to open .
Open workbook: Click here to open .
Delete sheet: Click here to open .

This Excel VBA Protect Sheet Without Password Tutorial is part of a more comprehensive series of Excel VBA Protect or Unprotect Sheet Tutorials.

Excel VBA Unprotect Sheet Without Password in 2 Easy Steps: Click here to open .
Excel VBA Protect Sheet with Password in 2 Easy Steps: Click here to open .
Excel VBA Unprotect Sheet with Password in 2 Easy Steps: Click here to open .
Excel VBA Protect Sheet Allow Filter in 2 Easy Steps: Click here to open .
Excel VBA Protect Sheet Allow Select Locked Cells in 4 Easy Steps: Click here to open .

You can find more Excel and VBA Tutorials in the organized Tutorials Archive: Click here to visit the Archives .

If you want to learn how to automate Excel (and save time) by working with macros and VBA, you may be interested in the following Premium Excel Macro and VBA Training Materials:

Premium Courses at the Power Spreadsheets Academy: Click here to open .
Books at the Power Spreadsheets Library: Click here to open .
VBA Cheat Sheets: Click here to open .

If you want to save time when working with macros and VBA, you may be interested in AutoMacro: Click here to learn more about AutoMacro (affiliate link). AutoMacro is an add-in for VBA that installs directly into the VBE. Depending on the version, AutoMacro comes loaded with:

Code generators.
An extensive code library.
The ability to create your own code library.
Advanced coding tools.

If you need consulting services, you may want to consider working with ExcelRescue. ExcelRescue is my usual suggestion for people who (like you) may need help with Excel tasks/projects: Click here to visit ExcelRescue (affiliate link).

The VBA Protect Sheet Without Password Snippet Template/Structure

The following is the VBA protect sheet without password snippet template/structure I explain (step-by-step) in the Sections below.

The Example Before VBA Protect Sheet Without Password

The example workbook has a single (empty) worksheet.

The name of the example worksheet is “Protect Sheet Without Password”.
This is the sheet the VBA protect sheet without password example macro I create (by following the step-by-step process below) works with. In other words: The VBA protect sheet without password example macro protects this sheet without a password.

The image below displays the example worksheet before I execute the VBA protect sheet without password example macro.

Before I execute the VBA protect sheet without password example macro, the example worksheet is unprotected. Notice the Protect Sheet button (inside the Protect group of commands) in the Excel Ribbon (indicating the example worksheet is (currently) unprotected).

Example Excel workbook before VBA protect sheet without password example macro

Step 1: Refer to Sheet

Refer to the sheet you want to protect without password.

In other words: Create a VBA expression that returns an object representing the applicable sheet (you want to protect without a password). As a general rule: Work with 1 of the following objects:

A Worksheet object, representing a worksheet.
A Chart object, representing a chart sheet.

Consider explicitly including the following references to create a fully qualified object reference returning the applicable Worksheet (representing a worksheet) or Chart (representing a chart sheet) object:

The Application.ThisWorkbook property.
The Application.Workbooks and Workbooks.Item properties.
The Application.ActiveWorkbook property.
The Workbook.Sheets and Sheets.Item properties.
The Workbook.Worksheets and Worksheets.Item properties.
The Workbook.Charts and Charts.Item properties.
The Application.ActiveSheet property.
The Workbook.ActiveChart property.

Step 1 Example

Refer to the worksheet named “Protect Sheet Without Password” inside the workbook where the procedure is stored.
The Application.ThisWorkbook property: ThisWorkbook.
The Workbook.Worksheets and Worksheets.Item properties: Worksheets(“Protect Sheet Without Password”).

Step 2: Protect Sheet Without Password

Call the applicable version of the Protect method:

Worksheet.Protect, if protecting a worksheet.
Chart.Protect, if protecting a chart sheet.

Step 2 Example

Considering the Worksheet object reference I created in step #1.

The full VBA protect sheet without password example macro is as follows:

The GIF below illustrates the effects of using the VBA protect sheet without password example macro.

Notice how:

The Protect Sheet button (inside the Protect group of commands) in the Excel Ribbon (indicating the example worksheet is (currently) unprotected);
Is replaced by the Unprotect Sheet button (indicating the example worksheet is now protected) when I execute the VBA protect sheet without password example macro.

Download the VBA Protect Sheet Without Password Example Workbook

This Excel VBA Protect Sheet Without Password Tutorial is accompanied by an Excel workbook with the data and VBA code I use when describing the step-by-step process above. Get this example workbook (for free) by clicking the button below .

The following Excel Macro and VBA Tutorials may help you better understand and implement the contents above.

I publish a lot of Tutorials and Training Resources about Microsoft Excel and VBA . Here are some of my most popular Excel Training Resources:

Free Excel VBA Email Course
Excel Macro Tutorial for Beginners
Excel Power Query (Get and Transform) Tutorial for Beginners
Excel Keyboard Shortcut Cheat Sheet
Excel Resources

Mobile Menu Overlay

The White House 1600 Pennsylvania Ave NW Washington, DC 20500

FACT SHEET: President Biden Issues Executive Order on Safe, Secure, and Trustworthy Artificial Intelligence

Today, President Biden is issuing a landmark Executive Order to ensure that America leads the way in seizing the promise and managing the risks of artificial intelligence (AI). The Executive Order establishes new standards for AI safety and security, protects Americans’ privacy, advances equity and civil rights, stands up for consumers and workers, promotes innovation and competition, advances American leadership around the world, and more. As part of the Biden-Harris Administration’s comprehensive strategy for responsible innovation, the Executive Order builds on previous actions the President has taken, including work that led to voluntary commitments from 15 leading companies to drive safe, secure, and trustworthy development of AI. The Executive Order directs the following actions: New Standards for AI Safety and Security

As AI’s capabilities grow, so do its implications for Americans’ safety and security. With this Executive Order, the President directs the most sweeping actions ever taken to protect Americans from the potential risks of AI systems :

Require that developers of the most powerful AI systems share their safety test results and other critical information with the U.S. government. In accordance with the Defense Production Act, the Order will require that companies developing any foundation model that poses a serious risk to national security, national economic security, or national public health and safety must notify the federal government when training the model, and must share the results of all red-team safety tests. These measures will ensure AI systems are safe, secure, and trustworthy before companies make them public.
Develop standards, tools, and tests to help ensure that AI systems are safe, secure, and trustworthy. The National Institute of Standards and Technology will set the rigorous standards for extensive red-team testing to ensure safety before public release. The Department of Homeland Security will apply those standards to critical infrastructure sectors and establish the AI Safety and Security Board. The Departments of Energy and Homeland Security will also address AI systems’ threats to critical infrastructure, as well as chemical, biological, radiological, nuclear, and cybersecurity risks. Together, these are the most significant actions ever taken by any government to advance the field of AI safety.
Protect against the risks of using AI to engineer dangerous biological materials by developing strong new standards for biological synthesis screening. Agencies that fund life-science projects will establish these standards as a condition of federal funding, creating powerful incentives to ensure appropriate screening and manage risks potentially made worse by AI.
Protect Americans from AI-enabled fraud and deception by establishing standards and best practices for detecting AI-generated content and authenticating official content . The Department of Commerce will develop guidance for content authentication and watermarking to clearly label AI-generated content. Federal agencies will use these tools to make it easy for Americans to know that the communications they receive from their government are authentic—and set an example for the private sector and governments around the world.
Establish an advanced cybersecurity program to develop AI tools to find and fix vulnerabilities in critical software, building on the Biden-Harris Administration’s ongoing AI Cyber Challenge. Together, these efforts will harness AI’s potentially game-changing cyber capabilities to make software and networks more secure.
Order the development of a National Security Memorandum that directs further actions on AI and security, to be developed by the National Security Council and White House Chief of Staff. This document will ensure that the United States military and intelligence community use AI safely, ethically, and effectively in their missions, and will direct actions to counter adversaries’ military use of AI.

Protecting Americans’ Privacy

Without safeguards, AI can put Americans’ privacy further at risk. AI not only makes it easier to extract, identify, and exploit personal data, but it also heightens incentives to do so because companies use data to train AI systems. To better protect Americans’ privacy, including from the risks posed by AI, the President calls on Congress to pass bipartisan data privacy legislation to protect all Americans, especially kids, and directs the following actions:

Protect Americans’ privacy by prioritizing federal support for accelerating the development and use of privacy-preserving techniques— including ones that use cutting-edge AI and that let AI systems be trained while preserving the privacy of the training data.
Strengthen privacy-preserving research and technologies, such as cryptographic tools that preserve individuals’ privacy, by funding a Research Coordination Network to advance rapid breakthroughs and development. The National Science Foundation will also work with this network to promote the adoption of leading-edge privacy-preserving technologies by federal agencies.
Evaluate how agencies collect and use commercially available information —including information they procure from data brokers—and strengthen privacy guidance for federal agencies to account for AI risks. This work will focus in particular on commercially available information containing personally identifiable data.
Develop guidelines for federal agencies to evaluate the effectiveness of privacy-preserving techniques, including those used in AI systems. These guidelines will advance agency efforts to protect Americans’ data.

Advancing Equity and Civil Rights

Irresponsible uses of AI can lead to and deepen discrimination, bias, and other abuses in justice, healthcare, and housing. The Biden-Harris Administration has already taken action by publishing the Blueprint for an AI Bill of Rights and issuing an Executive Order directing agencies to combat algorithmic discrimination , while enforcing existing authorities to protect people’s rights and safety. To ensure that AI advances equity and civil rights, the President directs the following additional actions:

Provide clear guidance to landlords, Federal benefits programs, and federal contractors to keep AI algorithms from being used to exacerbate discrimination.
Address algorithmic discrimination through training, technical assistance, and coordination between the Department of Justice and Federal civil rights offices on best practices for investigating and prosecuting civil rights violations related to AI.
Ensure fairness throughout the criminal justice system by developing best practices on the use of AI in sentencing, parole and probation, pretrial release and detention, risk assessments, surveillance, crime forecasting and predictive policing, and forensic analysis.

Standing Up for Consumers, Patients, and Students

AI can bring real benefits to consumers—for example, by making products better, cheaper, and more widely available. But AI also raises the risk of injuring, misleading, or otherwise harming Americans. To protect consumers while ensuring that AI can make Americans better off, the President directs the following actions:

Advance the responsible use of AI in healthcare and the development of affordable and life-saving drugs. The Department of Health and Human Services will also establish a safety program to receive reports of—and act to remedy – harms or unsafe healthcare practices involving AI.
Shape AI’s potential to transform education by creating resources to support educators deploying AI-enabled educational tools, such as personalized tutoring in schools.

Supporting Workers

AI is changing America’s jobs and workplaces, offering both the promise of improved productivity but also the dangers of increased workplace surveillance, bias, and job displacement. To mitigate these risks, support workers’ ability to bargain collectively, and invest in workforce training and development that is accessible to all, the President directs the following actions:

Develop principles and best practices to mitigate the harms and maximize the benefits of AI for workers by addressing job displacement; labor standards; workplace equity, health, and safety; and data collection. These principles and best practices will benefit workers by providing guidance to prevent employers from undercompensating workers, evaluating job applications unfairly, or impinging on workers’ ability to organize.
Produce a report on AI’s potential labor-market impacts , and study and identify options for strengthening federal support for workers facing labor disruptions , including from AI.

Promoting Innovation and Competition

America already leads in AI innovation—more AI startups raised first-time capital in the United States last year than in the next seven countries combined. The Executive Order ensures that we continue to lead the way in innovation and competition through the following actions:

Catalyze AI research across the United States through a pilot of the National AI Research Resource—a tool that will provide AI researchers and students access to key AI resources and data—and expanded grants for AI research in vital areas like healthcare and climate change.
Promote a fair, open, and competitive AI ecosystem by providing small developers and entrepreneurs access to technical assistance and resources, helping small businesses commercialize AI breakthroughs, and encouraging the Federal Trade Commission to exercise its authorities.
Use existing authorities to expand the ability of highly skilled immigrants and nonimmigrants with expertise in critical areas to study, stay, and work in the United States by modernizing and streamlining visa criteria, interviews, and reviews.

Advancing American Leadership Abroad

AI’s challenges and opportunities are global. The Biden-Harris Administration will continue working with other nations to support safe, secure, and trustworthy deployment and use of AI worldwide. To that end, the President directs the following actions:

Expand bilateral, multilateral, and multistakeholder engagements to collaborate on AI . The State Department, in collaboration, with the Commerce Department will lead an effort to establish robust international frameworks for harnessing AI’s benefits and managing its risks and ensuring safety. In addition, this week, Vice President Harris will speak at the UK Summit on AI Safety, hosted by Prime Minister Rishi Sunak.
Accelerate development and implementation of vital AI standards with international partners and in standards organizations, ensuring that the technology is safe, secure, trustworthy, and interoperable.
Promote the safe, responsible, and rights-affirming development and deployment of AI abroad to solve global challenges, such as advancing sustainable development and mitigating dangers to critical infrastructure.

Ensuring Responsible and Effective Government Use of AI

AI can help government deliver better results for the American people. It can expand agencies’ capacity to regulate, govern, and disburse benefits, and it can cut costs and enhance the security of government systems. However, use of AI can pose risks, such as discrimination and unsafe decisions. To ensure the responsible government deployment of AI and modernize federal AI infrastructure, the President directs the following actions:

Issue guidance for agencies’ use of AI, including clear standards to protect rights and safety, improve AI procurement, and strengthen AI deployment.
Help agencies acquire specified AI products and services faster, more cheaply, and more effectively through more rapid and efficient contracting.
Accelerate the rapid hiring of AI professionals as part of a government-wide AI talent surge led by the Office of Personnel Management, U.S. Digital Service, U.S. Digital Corps, and Presidential Innovation Fellowship. Agencies will provide AI training for employees at all levels in relevant fields.

As we advance this agenda at home, the Administration will work with allies and partners abroad on a strong international framework to govern the development and use of AI. The Administration has already consulted widely on AI governance frameworks over the past several months—engaging with Australia, Brazil, Canada, Chile, the European Union, France, Germany, India, Israel, Italy, Japan, Kenya, Mexico, the Netherlands, New Zealand, Nigeria, the Philippines, Singapore, South Korea, the UAE, and the UK. The actions taken today support and complement Japan’s leadership of the G-7 Hiroshima Process, the UK Summit on AI Safety, India’s leadership as Chair of the Global Partnership on AI, and ongoing discussions at the United Nations. The actions that President Biden directed today are vital steps forward in the U.S.’s approach on safe, secure, and trustworthy AI. More action will be required, and the Administration will continue to work with Congress to pursue bipartisan legislation to help America lead the way in responsible innovation. For more on the Biden-Harris Administration’s work to advance AI, and for opportunities to join the Federal AI workforce, visit AI.gov .

Stay Connected

We'll be in touch with the latest information on how President Biden and his administration are working for the American people, as well as ways you can get involved and help our country build back better.

Opt in to send and receive text messages from President Biden.

Entertainment
Photography
Press Releases
Israel-Hamas War
Russia-Ukraine War
Latin America
Middle East
Asia Pacific
Election 2024
AP Top 25 College Football Poll
Movie reviews
Book reviews
Financial Markets
Business Highlights
Financial wellness
Artificial Intelligence
Social Media

Voters protect abortion rights and Democrats prevail in Kentucky

Copy Link copied

Democrats have a lot to be happy about after winning major races in Tuesday’s election. Abortion rights supporters won big in an Ohio ballot measure and Democratic Gov. Andy Beshear was reelected in Kentucky after running television ads painting his challenger as extremist on abortion.

Follow along here for the latest updates.

Democrats notched two early wins Tuesday night in Kentucky and Ohio, both states that voted for Trump in 2020. In both states, abortion was the main issue.
In Kentucky, Democratic Gov. Andy Beshear was reelected in a state that Trump had won by 26 percentage points. Beshear had criticized the abortion views of his Republican challenger, Attorney General Daniel Cameron, in debates and television ads.
In Ohio, a ballot measure preserving abortion rights passed in a state that Trump won by eight percentage points in 2020. Republicans had already tried to derail the measure by calling an unusual August referendum to make it harder to pass ballot measures, an initiative that was roundly rejected by Ohio voters.

Former Biden White House aide Gabe Amo will become the first Black member of Congress from Rhode Island after winning the special election in that state’s first congressional district.

Philadelphia will have its first female mayor after Democrat Cherelle Parker defeated Republican David Oh in the overwhelmingly Democratic city.

Races we are still monitoring:

In Houston, voters are choosing from a crowded field that includes U.S. Rep. Sheila Jackson Lee and state Sen. John Whitmire , two longtime Democratic lawmakers. If elected, Jackson Lee would be Houston’s first Black female mayor.

Virginia Democrats held their majority in the state Senate on Tuesday, but control of the House of Delegates remained unsettled late into the evening, with vote counting still underway in key races.

Mississippians endured long lines and some polling places ran out of ballots Tuesday, requiring judges to intervene, as the state tried to settle a hard-fought governor’s race between Republican incumbent Tate Reeves and Democratic challenger Brandon Presley .

Voters in Pennsylvania’s second-largest county have reelected their longtime district attorney who lost his Democratic primary and ran in the general election as a Republican.

Steve Zappala bested progressive Democrat Matt Dugan in a rematch for district attorney of Allegheny County, which includes Pittsburgh.

“In terms of my politics, I’m not a politician,” Zappala said during a debate in October. “I don’t care about the GOP. I don’t care about the Democrats.”

Zappala defended his record as DA and said his support came from the county. He also dismissed his opponent as having out-of-state financial support.

Voters have elected women for the first time to lead Philadelphia and Pennsylvania’s second-largest county, installing Cherelle Parker as the 100th mayor of the state’s largest city and Sara Innamorato as executive of the county that includes Pittsburgh.

Parker, 51, who has held office at the state and local level after first becoming involved in politics as a teenager, emerged from a crowded field of Democrats in the May primary as the only leading Black candidate. She will replace term-limited Democratic Mayor Jim Kenney.

Across the state in Allegheny County, Innamorato, 37, defeated Republican Joe Rockey, a political newcomer. The former state legislator who campaigned on progressive models to modernize county government will replace Democrat Rich Fitzgerald, who was term-limited.

To the tune of “Ladies First,” an emotional Parker addressed supporters at her election night watch party.

Who is Cherelle Parker going to be? A get-it-done Philadelphian. A get-it-done mayor who won’t ever forget her deep roots.

Who is Cherelle Parker going to be? A get-it-done Philadelphian. A get-it-done mayor who won’t ever forget her deep roots. I’m Philly-born, I’m Philly-bred and I’ll be Philadelphian ’til I’m dead.

A pair of Democrats, U.S. Rep. Sheila Jackson Lee and state Sen. John Whitmire , received the most votes out of 18 candidates, but neither could get the majority needed to avoid a runoff.

Read the full story

The Democrats’ victory in the upper chamber ended the prospect of a Republican trifecta that would have allowed Gov. Glenn Youngkin to swiftly move on conservative policy priorities that Senate Democrats have been able to stymie in his first two years in office. The chamber has been under Democratic control since 2020.

On Tuesday, Ohio voters approved a constitutional amendment that guarantees the right to abortion and other forms of reproductive health care. (Nov. 7)

Officials are refuting online claims that a gas leak at one polling place in Kentucky’s largest county was election rigging that helped Democratic Gov. Andy Beshear win reelection.

The claims amassed thousands of shares on social media Tuesday after reports of a gas leak at Highland Baptist Church in Louisville caused polls there to close for about 30 minutes while Louisville Gas & Electric investigated the issue. The delay prompted a judge to extend voting at the church until 6:30 p.m. instead of 6 p.m. EST.

Social media users questioned whether the gas leak was real and insinuated that extended voting hours in a Democratic county gave Beshear the votes he needed to win.

The delay was prompted by a legitimate report of gas emitting from a stove in the church, said Chris Whelan, a spokesperson for Louisville Gas & Electric. Gas was detected, but not at hazardous levels, she said. The stove was turned off and it dissipated.

This was a legitimate instance of a gas leak so any claims otherwise, we just think are patently absurd.

Huber added that only one voter came to cast a ballot between 6 and 6:30 p.m. at either of the two polling places in the county where a judge extended voting hours. The other polling place, an elementary school, was temporarily shut down Tuesday morning because of a police pursuit.

Two Democrats on the 20-member Bridgeport, Connecticut, City Council have been arrested in separate incidents at the same polling place on Election Day.

A police spokesperson says Councilmember Samia Suliman was charged with breach of peace after police received a 911 call late Tuesday afternoon about a person causing a disturbance outside the John F. Kennedy School.

Less than an hour later, police arrested Councilmember Maria Pereira and charged her with assaulting a 70-year-old woman on the same school property. Pereira was on the ballot Tuesday.

Police didn’t explain the circumstances of either arrest and declined to say how Suliman disturbed the peace.

Text messages seeking comment from the women were not immediately returned.

The Founding Fathers didn’t establish a national clearinghouse for counting the vote, and the states all do it a little differently.

So, every U.S. election night since 1848, The Associated Press has tallied millions of ballots and determined which candidates have won their races.

Read how it’s done

A Uvalde mother who lost her bid for mayor says she’ll never stop fighting for her daughter, Lexi, who was among the 19 children killed in last year’s school shooting rampage.

I meant it when I said this was only the beginning. After all, I’m not a regular mom. I’m Lexi’s mom.

She lost to Cody Smith, a former mayor of Uvalde who left office in 2012. He will complete the term of Mayor Don McLaughlin, who criticized police in Texas over their hesitant response to the shooting and is resigning to seek a seat in the state legislature.

Mata-Rubio, who pressed Uvalde leaders for answers and transparency after the shooting, campaigned on unifying the small town that is still riven with division . Her candidacy put on the ballot one of Uvalde’s most outspoken figures and an aggressive proponent of stricter gun laws.

President Joe Biden and Vice President Kamala Harris are cheering Ohio’s vote to ensure access to abortion and other forms of reproductive health care.

Biden said in a statement Tuesday that “democracy won” in Ohio.

The Democratic president says the state’s voters rejected “attempts by MAGA Republican elected officials to impose extreme abortion bans that put the health and lives of women in jeopardy.”

Harris used the win in Ohio to call for Congress to pass a bill restoring the federal abortion protections that were lost after the reversal of Roe v. Wade.

She said in a statement, “When they do, President Biden will sign it into law.”

Political candidates broke barriers in a handful of wins Tuesday.

And Philadelphia will have its first female mayor after Democrat Cherelle Parker defeated Republican David Oh in the overwhelmingly Democratic city.

More highlights from Election Day 2023

Despite a spike in home values, Colorado voters rejected a measure that would have lowered property tax bills for homeowners.

Democratic Kentucky Gov. Andy Beshear says his reelection victory “sends a loud, clear message” that there should be an end to “anger politics.”

Beshear said in his victory remarks on Tuesday that Kentucky voters had made “a choice not to move to the right or to the left but to move forward for every single family.”

Beshear defeated Republican challenger Daniel Cameron, who had been endorsed by former President Donald Trump . Beshear said he had been up against a number of GOP-funded, third-party groups that were running ads “full of hate and division.”

“And you know what?” Beshear asked. “We beat ’em all at the same time.”

In Uvalde’s first mayoral race since the Robb Elementary School shooting , former mayor Cody Smith won back the job Tuesday over Kimberly Mata-Rubio, a mother who has led calls for tougher gun laws since her daughter was among the 19 children killed in the 2022 attack.

The race tested the mood of the South Texas town more than a year after one of America’s deadliest mass shootings and a botched police response that remains under criminal investigation . Two teachers were also killed in the attack, which was carried out by a teenage gunman with an AR-style rifle.

Ohio voters approved a ballot proposal legalizing recreational marijuana on Tuesday, defying Republican legislative leaders who had failed to pass the proposed law.

Passage of Issue 2 makes Ohio the 24th state to allow adult cannabis use for non-medical purposes. The new law will allow adults 21 and over to buy and possess up to 2.5 ounces of cannabis and to grow plants at home. A 10% tax will be imposed on purchases, to be spent on administrative costs, addiction treatment, municipalities with dispensaries and social equity and jobs programs supporting the industry itself.

With Tuesday’s vote to enshrine abortion rights into its state constitution, Ohio is the seventh state where voters have protected abortion access since the U.S. Supreme Court overturned Roe v. Wade last year.

Voters reacts to the passage of Ohio Issue 1, a ballot measure to amend the state constitution and establish a right to abortion, at an election night party hosted by the Hamilton County Democratic Party, Tuesday, Nov. 7, 2023, at Knox Joseph Distillery in the Over-the-Rhine neighborhood of Cincinnati. (Kareem Elgazzar/The Cincinnati Enquirer via AP)

Voters in both Democratic and Republican states — California, Kansas, Kentucky, Michigan, Montana and Vermont — have either affirmed abortion access or turned back attempts to undermine it.

Ohio was the only state to consider a statewide abortion rights question this year, and some view its decision as a signal of how voters nationwide will be feeling about the issue ahead of the 2024 presidential race.

Democrat Gabe Amo says that he’s “humbled” to be elected Rhode Island’s first Black representative to Congress

but that he “didn’t run to make history.”

Amo told The Associated Press shortly after his win over Republican Gerry Leonard on Tuesday that he sees himself as part of a long line of advocates, whether they were people of color or fighting for the rights of woman or working people.

Gabe Amo meets with members of the press at an election results party after his win in the Democratic primary for Rhode Island's 1st Congressional District, Tuesday, Sept. 5, 2023, in Pawtucket, R.I. (David Delpoio/Providence Journal via AP)

(David Delpoio/Providence Journal via AP)

The former White House aide succeeds former Democratic Rep. David Cicilline, who stepped down this summer to become president and CEO of the Rhode Island Foundation.

The son of Ghanaian and Liberian immigrants, Amo emerged victorious from a crowded Democratic field in the September primary, claiming more than 32% of the vote.

It’s officially a two-term trend for the Beshear family.

Kentucky Gov. Andy Beshear has joined his father, Steve Beshear, as a twice-elected top Democratic leader of the state.

The younger Beshear won his second term Tuesday by defeating Republican Attorney General Daniel Cameron. With it, Andy Beshear is also positioning himself to join a growing list of Democratic governors flagged as potential contenders for higher office nationally.

By the end of Andy Beshear’s second four-year term, a Beshear will have presided in the Kentucky governor’s office for 16 of the last 20 years. Steve Beshear served from 2007 to 2015.

FILE - New York City Council candidate Yusef Salaam speaks during an interview with The Associated Press, Wednesday, May 24, 2023, in New York. (AP Photo/Mary Altaffer, File)

(AP Photo/Mary Altaffer, File)

Exonerated “Central Park Five” member Yusef Salaam won a seat Tuesday on the New York City Council, marking a stunning reversal of fortune for a political newcomer who was wrongly imprisoned as a teenager in the infamous rape case.

Salaam, a Democrat, will represent a central Harlem district on the City Council, having run unopposed for the seat in one of many local elections happening across New York state on Tuesday. He won his primary election in a landslide.

Ohio voters approved a constitutional amendment on Tuesday that ensures access to abortion and other forms of reproductive health care, the latest victory for abortion rights supporters since the U.S. Supreme Court overturned Roe v. Wade last year.

Ohio became the seventh state where voters decided to protect abortion access after the landmark ruling and was the only state to consider a statewide abortion rights question this year.

Kentucky Governor and Democratic candidate for re-election Andy Beshear speaks to supporters during a stop of his statewide bus tour in Richmond, Ky., Monday, Oct. 30, 2023. (AP Photo/Timothy D. Easley)

(AP Photo/Timothy D. Easley)

Kentucky Gov. Andy Beshear won reelection to a second term Tuesday, notching another significant statewide victory in an increasingly red state that could serve as a model for other Democrats on how to thrive politically heading into next year’s defining presidential election.

Beshear, 45, rode his stewardship over record economic growth and his handling of multiple disasters, from tornadoes and floods to the COVID-19 pandemic, to victory over Republican Attorney General Daniel Cameron , a protege of Senate Minority Leader Mitch McConnell .

A judge in Mississippi’s largest county has extended voting times to 10 p.m. EST at four voting precincts after they experienced ballot shortages and long lines.

The order from a special judge in Hinds County came after a nonpartisan group, Mississippi Votes, filed a petition to extend voting in the four precincts in some Jackson suburbs.

The four precincts where voting will be allowed until 10 p.m. EST are Byram City Hall, a United Methodist church in Raymond, and Wildwood Baptist Church and Northside Baptist Church, both in Clinton.

A different judge already had extended voting throughout the county until 9 p.m. EST in response to the ballot shortages.

(AP Photo/Matt Rourke)

Cherelle Parker, a Democrat who has held office at the state and local level after first becoming involved in politics as a teenager, was elected Tuesday as Philadelphia’s 100th mayor, becoming the first woman to hold the post.

Parker, 51, emerged from a crowded field of Democrats in the May primary and was heavily favored over Republican David Oh in the city, a Democratic stronghold. She will replace Democrat Jim Kenney, who was ineligible for reelection due to term limits.

Two precincts in a Philadelphia suburb will remain open an extra hour after a bomb threat forced the evacuation of a polling place.

A Delaware County spokesperson says the county solicitor sought and won the extension from a judge after a threat forced the evacuation of Radnor High School and polling places for two precincts to move to Radnor Elementary in Wayne. Officials say police are investigating the threat.

Polls will remain open there until 9 p.m. Eastern time.

The state’s top-of-the-ticket race is an open seat for state Supreme Court , which could play an important role in settling legal challenges in the 2024 presidential election in the battleground state.

Gabe Amo, Rhode Island Democratic candidate for the U.S. House, center, greets people during a campaign stop at a cafe, Monday, Nov. 6, 2023, in Providence, R.I. (AP Photo/Steven Senne)

(AP Photo/Steven Senne)

Democrat Gabe Amo defeated Republican Gerry Leonard to win Rhode Island’s 1st Congressional District seat Tuesday, becoming the state’s first Black candidate elected to Congress.

A judge has extended voting times in Mississippi’s largest county by an hour, until 9 p.m. Eastern time, after several polling places had ballot shortages and long lines.

The order came after the Mississippi Democratic Party filed a petition in one court and a nonpartisan group, Mississippi Votes, filed a separate petition in another court and its request was backed by the Mississippi Republican Party.

The ballot shortages in Hinds County were reported in several Jackson suburbs. Polls in Mississippi were supposed to be open 12 hours, from 8 a.m. to 8 p.m. ET.

The problems in Hinds County happened after the county’s poll worker training was delayed by a September breach involving the county’s computer system.

Credell Calhoun, a Hinds County supervisor, said he had heard of at least seven Hinds County precincts that ran out of ballots at some point Tuesday. He said the Hinds County election commissioners underestimated turnout.

This is way beyond anything we’ve ever seen in the electoral process. As hard as we worked to get the vote out and then you’re going to have stupidity to not have enough ballots.

For more photos follow @APNews on Instagram.

Polls are closing in Kentucky and Virginia in pivotal off-year elections.

Voting closed at 7 p.m. ET Tuesday across Virginia, where Democrats and Republicans have campaigned for legislative control by arguing the other party is wrong on abortion. Polls had also closed across Kentucky by 7 p.m. ET as voters decide whether to give Democratic Gov. Andy Beshear a second term.

Over the next hour, polls will also close in Ohio, where voters are considering a constitutional amendment supported by abortion rights groups.

At 8 p.m. ET, voting closes in six other states, as well as Texas counties in the Central Time Zone.

An hour later, polls close in the rest of Texas’ counties, as well in New York.

A key race in Virginia’s pivotal legislative elections hinges on a Democrat whose campaign was upended by revelations she engaged in sex acts with her husband on a pornographic website.

Susanna Gibson is running against Republican businessman David Owen in one of the state’s most competitive districts after all 100 seats in the House of Delegates were redrawn to conform with the 2020 census.

Some people wrote off Gibson’s chances after The Washington Post reported in September about her participation in livestreamed sex , which included soliciting payments from viewers in exchange for specific acts.

But Gibson, a nurse practitioner, refused to withdraw from the race and accused Republicans of dirty politics for exposing her conduct. She has largely ignored the allegations and focused on abortion rights, which Democrats said could be in jeopardy if Republicans gain control of the Legislature.

Polls have closed in parts of Kentucky as voters consider whether to give Democratic Gov. Andy Beshear a second term or replace him with Republican Daniel Cameron.

Much of Beshear’s first term was dominated by his response to a series of natural disasters and the pandemic. But his reelection campaign often focused on dire warnings about the future of abortion rights. He portrayed Cameron as too extreme on the issue, pointing to his support for the state’s abortion ban , which lacks exceptions for pregnancies caused by rape or incest.

Cameron would be the nation’s first Black Republican to be elected governor if he wins. He has reaffirmed his support for the current Kentucky law, which bans all abortions except when carried out to save a pregnant woman’s life or to prevent a disabling injury.

Authorities have arrested a man accused of wielding a flag attached to a fishing pole as he made threatening gestures to voters and damaged a voting machine at a Kentucky polling location.

The 40-year-old Louisville man was charged with interfering with election and tampering and destruction of a voting machine. Both are felony counts under state law. He was also charged with two misdemeanor counts of menacing.

Erran Huber, a spokesperson for the Jefferson County Clerk’s Office, said no votes were compromised and voting continued at the polling location.

The man was taken to the Louisville Metro Department of Corrections for booking. A phone listing for him went straight to voicemail and did not accept messages Tuesday.

Officials say a coding error in an eastern Pennsylvania county caused votes to be flipped on a ballot question that asked whether a pair of incumbent state appeals judges should be retained.

Voters are deciding whether Pennsylvania Superior Court Judges Jack Panella and Victor Stabile should be retained for additional 10-year terms. Officials in Northampton County say the “yes” or “no” votes for each judge were being switched because of the error. If a voter marked “yes” to retain Panella and “no” on Stabile, for example, it was reflected as “no” on Panella and “yes” on Stabile.

County officials say voters first noticed the error, which affected all the county’s voting machines in use on Tuesday. They plan to correct the error when votes are tabulated.

Officials say the coding error was limited to the county and only affected the judicial retention question.

Uvalde mayoral candidate Kimberly Mata-Rubio, center, hands out bracelets before the second annual Lexi’s Legacy Run, Saturday, Oct. 21, 2023, in Uvalde, Texas. (AP Photo/Darren Abate)

A Uvalde mother who has pushed for tougher gun laws after her daughter was among the 19 children killed in the Robb Elementary School attack is on the ballot Tuesday in a bid to become mayor of the South Texas town, which was left divided by one of America’s deadliest mass shootings.

Kimberly Mata-Rubio , 34, would become Uvalde’s first female mayor and has talked about charting a new direction for the town of 15,000 residents, where differences persist over how to move forward from the tragedy. That includes continued calls for accountability over the hesitant response by police , who did not confront the teenage gunman for more than an hour.

Less than two weeks after 18 people were killed by a gunman in their small New England city, residents headed gingerly cast ballots Tuesday for a slate of municipal races in an election that took on a more subdued and somber tone after the tragedy.

The mood in Lewiston, Maine, was somber. Several shooting survivors remained hospitalized, flags flew at half-staff, and funerals were being held this week for those who died in the attack.

Citing civic duty and a quest to return the community to normal life, Lewiston residents turned out to vote in several high-profile referendums and local races.

“This is a necessity. We have to do this. So we can’t neglect it even though we’ve been through a terrible tragedy,” said James Scribner, 79, a retired teacher and Marine veteran, who was joined by his wife at local school that was transformed into a polling place.

Voter arrive at a polling place at the Gov. James B. Longley Campus, Tuesday, Nov. 7, 2023, in Lewiston, Maine. The American flag still flies at half mast in honor of the 18 people who were killed in mass shootings less than two weeks ago. (AP Photo/Robert F. Bukaty)

Democratic nominee for governor Brandon Presley, right, and his wife Katelyn Mabus Presley hold up their “I Voted” stickers outside their Nettleton, Miss. precinct at the Nettleton Methodist Church Life Center on Tuesday, Nov. 7, 2023. Presley faces incumbent Mississippi Republican Gov. Tate Reeves, who is seeking reelection. (AP Photo/HG Biggs)

Republican Mississippi Gov. Tate Reeves, receives his ballot from Jackson, Miss., poll worker Joyce Lewis, 67, Tuesday, Nov. 7, 2023. Reeves, who is seeking reelection, faces Democratic nominee Brandon Presley. (AP Photo/Rogelio V. Solis)

Democratic nominee for governor Brandon Presley laughs as election official Kim Clayton checks his ID in his Nettleton, Miss. precinct at the Nettleton Methodist Church Life Center on Tuesday, Nov. 7, 2023. (AP Photo/HG Biggs)

A voter reaches for a “I Voted!” pen to mark her ballot at this north Jackson, Miss., precinct, Tuesday, Nov. 7, 2023. Statewide offices as well as a number of local and county offices are on the ballot. (AP Photo/Rogelio V. Solis)

Mississippi Republican Gov. Tate Reeves, enters his Jackson, Miss., precinct to vote, Tuesday, Nov. 7, 2023. Reeves, seeks reelection, and faces Democratic nominee Brandon Presley. (AP Photo/Rogelio V. Solis)

State election websites in Kentucky and Mississippi , two states featuring key governors’ races, experienced some issues early on Election Day.

In Mississippi, voters looking up details about their polling location experienced slow loading times, according to Elizabeth Holbert Jonson, spokesperson for the Mississippi Secretary of State’s office.

She said that increased traffic to the website caused the problem and that an additional server was added that “appears to have resolved the issue.”

But the polling place locator was still spotty late Tuesday morning. The Associated Press checked addresses in several cities — including Jackson, Madison, Gulfport, Greenwood and Marks — and it wasn’t working.

“We encourage folks to type slowly in the residential address box,” Holbert Jonson said. “When doing that, the system should auto-populate the addresses.”

In Kentucky, a spike in traffic to the State Board of Election’s website disrupted access for a “short period of time,” according to Michon Lindstrom, spokesperson for the Kentucky Secretary of State’s office. Issues were resolved and officials don’t expect further issues, Lindstrom said.

An attendee uses a sign to shield the sun during a "rosary rally" on Sunday, Aug. 6, 2023, in Norwood, Ohio. A national religious organization, Catholics for Catholics, gathered a lineup of anti-abortion influencers and conspiracy theorists from across the U.S. to speak at the rally to urge a “yes” vote on a ballot question in Ohio, known as Issue 1. If voters approve Issue 1, it would make it more difficult for an abortion rights amendment on the November ballot to succeed. (AP Photo/Darron Cummings)

An attendee uses a sign to shield the sun during a “rosary rally” on Sunday, Aug. 6, 2023, in Norwood, Ohio.(AP Photo/Darron Cummings)

Ohio became the latest flashpoint Tuesday in the nation’s battle over abortion access since the U.S. Supreme Court overturned a constitutional right to the procedure last year.

Voters are deciding whether to pass a constitutional amendment guaranteeing an individual right to abortion and other forms of reproductive healthcare.

FILE - Virginia Gov. Glenn Youngkin speaks to supporters as he attends a campaign rally on Monday, Oct. 31, 2022, in Westchester, N.Y. One year after Glenn Youngkin became the first Republican in more than a decade to win the Virginia governorship, some in his party believe the shine of his national star is being tested just as he quietly contemplates a 2024 presidential run. (AP Photo/Eduardo Munoz Alvarez, File)

Virginia’s closely watched legislative campaign cycle closes out Tuesday, as voters decide whether to empower Republicans with full state government control or let Democrats keep serving as a bulwark against Gov. Glenn Youngkin’s agenda.

The outcome in Virginia — among just four states with legislative elections this year — will be closely scrutinized nationwide for hints of what may come in the 2024 presidential cycle.

FILE - Taylor Swift performs during “The Eras Tour” in Nashville, Tenn., on May 5, 2023. Swift is releasing her “Taylor Swift: The Eras Tour” concert film on Oct. 13. (AP Photo/George Walker IV, File)

Taylor Swift is encouraging her pack of Swifties to head to the polls on Election Day.

“Voters gonna vote!” Swift wrote in a post to her 275 million Instagram followers, riffing off the lyrics to her hit single “Shake It Off.”

In the post, she encouraged those registered to vote in Colorado, Kentucky, Maine, Mississippi, New Jersey, New York, Ohio, Pennsylvania, Texas and Virginia to “use your voice” and directed them to the nonpartisan voter advocacy group Vote.org for more information.

Vote.org says nearly 40,000 people registered to vote on their website after Swift mentioned the group on National Voter Registration Day in September.

“Taylor Swift is one of the busiest people in the world, but she always has time to stand up for democracy, use her own voice and encourage her fans and followers to use theirs,” Vote.org CEO Andrea Hailey said in a statement Tuesday.

Polls have begun to open in a few states for off-year elections that could give hints of voter sentiment ahead of next year’s critical presidential contest.

In Kentucky, Democratic Gov. Andy Beshear is hoping that his support of abortion rights will persuade voters in his Republican-leaning state to look past their skepticism of the national party and give him another term in office.

Ohio is the only state to consider a statewide abortion rights question this year. Voters there will decide whether to pass a constitutional amendment guaranteeing an individual right to abortion and other forms of reproductive health care.

In Virginia, party control of the state legislature is up for grabs in what Republican Gov. Glenn Youngkin is calling the “most important elections in America.” The results will determine whether Republicans are empowered with full state government control or Democrats can continue serving as a bulwark against Youngkin’s agenda.

Uvalde mayoral candidate Kimberly Mata-Rubio, left, and campaign manager Dr. Laura Barberena, canvass a neighborhood in support of Mata-Rubio's campaign, Saturday, Oct. 21, 2023, in Uvalde, Texas. (AP Photo/Darren Abate)

Uvalde mayoral candidate Kimberly Mata-Rubio, left, canvasses a neighborhood. (AP Photo/Darren Abate)

Kimberly Mata-Rubio’s 10-year-old daughter was one of the 19 children and two teachers killed in the May 24, 2022, shooting in Uvalde, Texas. After a year of advocating for gun control and school safety at the state and federal level, Mata-Rubio is getting her start in politics at the local level by running to become Uvalde’s first female mayor.

The election set for Tuesday will test how the town chooses to move forward from a tragedy that some residents are ready to put behind them while others demand answers.

Mississippi’s long history of voter suppression has created widespread voter fatigue among Black residents in a state where they account for nearly 40% of the population. But this year’s elections have given reason for renewed optimism.

The governor’s race appears competitive and is attracting national attention. Tuesday’s election also happens to be the first one to be held without the burden of an unusual post-Reconstruction constitutional provision that had made it virtually impossible for Black candidates to win on a statewide basis.

Five Black candidates are running for statewide office and are hoping to become the first Black Mississippians to win one of those posts.

▶ Read the full story.

IMAGES

VBA WorkSheets
VBA Activate Sheet
VBA Activate Sheet
VBA WorkSheets
How To Loop Through Data With VBA With Statement
[Solved]-How to set a sheet by codename to pass to a UDF?-VBA Excel

COMMENTS

vba
1 Further to what @Rory said, if you pass the name as a string, you'll still have to set the worksheet in the sub ... like Set ws = TempBook.Sheets (sName) assuming sName is the string you pass into the sub, instead of ws As Worksheet - Scott Holtzman Nov 24, 2015 at 16:34 Add a comment 2 Answers Sorted by: 3
Working with Worksheets using Excel VBA (Explained with Examples)
In Excel VBA: The 'Worksheets' collection refers to the collection of all the worksheet objects in a workbook. In the above example, the Worksheets collection would consist of three worksheets. The 'Sheets' collection would refer to all the worksheets as well as chart sheets in the workbook.
Worksheet.Protect method (Excel)
Variant. A string that specifies a case-sensitive password for the worksheet or workbook. If this argument is omitted, you can unprotect the worksheet or workbook without using a password. Otherwise, you must specify the password to unprotect the worksheet or workbook. If you forget the password, you cannot unprotect the worksheet or workbook.
Using Excel worksheet functions in Visual Basic
In Visual Basic, the Excel worksheet functions are available through the WorksheetFunction object. The following Sub procedure uses the Min worksheet function to determine the smallest value in a range of cells. First, the variable myRange is declared as a Range object, and then it is set to range A1:C10 on Sheet1.
Passing arguments efficiently (VBA)
09/13/2021 6 contributors Feedback All arguments are passed to procedures by reference, unless you specify otherwise. This is efficient because all arguments passed by reference take the same amount of time to pass and the same amount of space (4 bytes) within a procedure regardless of the argument's data type.
VBA Protect Sheet
Step 1: Select the Sheet which needs to be protected. The first step is to decide which sheet we need to protect using a password to protect the sheet. Next, we need to call the sheet by name using the VBA Worksheet Object. For example, assume you want to protect the "Master Sheet" sheet, then you need to mention the worksheet name below.
Passing a Worksheet into a Sub()
MacOS. Feb 16, 2017. #8. Re: Passing a Worksheet into a Sub (), HELP! Just pass the argument along: Code: Myfile = Application.GetOpenFilename (Title:=Title) You can probably see why it's better to use a variable name that isn't also the name of a parameter. 0.
VBA Sheets
VBA Worksheets Cheatsheet This is the ultimate guide to working with Excel Sheets / Worksheets in VBA. At the bottom of this guide, we've created a cheat sheet of common commands for working with sheets. Sheets Vs. Worksheets
VBA ActiveSheet & How To Activate or Select a Sheet
The Activate Sheet command will actually "go to" the sheet, changing the visible Sheet. The above example uses the Sheet (Tab) name. Instead you can use the VBA code name for the worksheet: Sheet1.Activate ActiveSheet Name To get the ActiveSheet Name: msgbox ActiveSheet.name
excel
excel - Cracking Sheet Password with VBA - Stack Overflow Cracking Sheet Password with VBA Ask Question Asked 9 years, 11 months ago Modified 2 years, 4 months ago Viewed 58k times 19 I found this VBA code to unlock sheets without knowing the password:
Use Worksheet Functions in a Macro
To access an Excel function in VBA add Application.WorksheetFunction in front of the function that you wish to call. In the example below, we'll call Excel's Max Function: Dim maxvalue as long maxvalue = Application.WorksheetFunction.Max(Range("a1").Value, Range("a2").Value) The syntax of the functions are the same, however you will enter ...
Worksheet.Paste method (Excel)
Parameters. A Range object that specifies where the Clipboard contents should be pasted. If this argument is omitted, the current selection is used. This argument can be specified only if the contents of the Clipboard can be pasted into a range. If this argument is specified, the Link argument cannot be used.
Worksheet.Activate method (Excel)
Makes the current sheet the active sheet. Syntax. expression.Activate. expression A variable that represents a Worksheet object. Remarks. Calling this method is equivalent to choosing the sheet's tab. Example. This example activates Sheet1. Worksheets("Sheet1").Activate Support and feedback. Have questions or feedback about Office VBA or this ...
Worksheet object (Excel)
VB Dim strPassword As String strPassword = InputBox ("Enter the password for the worksheet") Worksheets ("Sheet1").Protect password:=strPassword, scenarios:=True When a worksheet is the active sheet, you can use the ActiveSheet property to refer to it.
Excel VBA protect worksheet with password
Before doing any worksheet Protection, the first thing to do is to ensure the Cell you want to protect is locked. Right click on a Cell > Format Cells > Protection > check Locked. To lock a Cell in VBA. Range ("A1").Locked = TRUE. To unlock a Cell in VBA. Range ("A1").Locked = FALSE.
How can I pass an array (worksheets) to a function in VBA?
1 Answer Sorted by: 1 Use Sheets instead of Worksheets - and don't declare the parameter as an array (= brackets after arr) Sheets already returns an array.
The Complete Guide To The VBA Worksheet
This post provides a complete guide to using the Excel VBA Worksheet in Excel VBA. If you want to know how to do something quickly then check out the quick guide to the VBA Worksheet If you are new to VBA then this post is a great place to start. I like to break things down into simple terms and explain them in plain English without the jargon.
Worksheet.Range property (Excel)
Cell1 and Cell2 can be Range objects that contain a single cell, column, row, or any other range of cells. Often, Cell1 and Cell2 are single cells in the upper-left and lower-right corners of the range returned. When used without an object qualifier, this property is a shortcut for ActiveSheet.Range (it returns a range from the active sheet; if ...
Excel VBA Protect Sheet Without Password in 2 Easy Steps (+ Example)
Step 1: Refer to Sheet. Refer to the sheet you want to protect without password. In other words: Create a VBA expression that returns an object representing the applicable sheet (you want to protect without a password). As a general rule: Work with 1 of the following objects:
FACT SHEET: President
FACT SHEET: President Biden Issues Executive Order on Safe, Secure, ... the President calls on Congress to pass bipartisan data privacy legislation to protect all Americans, especially kids, and ...
Democrats prevail in Kentucky and Virginia. Ohio protects abortion
A proposal to enshrine abortion rights in Ohio's Constitution was approved in a statewide election Tuesday, with a significant number of Republicans joining with Democrats to ensure the measure's passage.. The Associated Press has called the race, with votes cast in favor of proposal winning with about 56% of the vote, compared with about 44% for votes cast in opposition — a lead of ...