Tag: Excel VBA Macro

This post will guide you how to Copy a Range of Selection to a Text file in Excel. How do I copy/paste a range from the current worksheet to a notepad text file with VBA Macro in Excel 2013/2016/2019/365.

Assuming that you have a list of data in range B1:B5 in your worksheet, and you wish to copy this range to another worksheet or a notepad text file, and save it as a .txt or xlsx. You can copy the selection to the clipboard by manually, open up a Notebook file or what ever text application you use, or you can open an blank worksheet, then paste it in there and save as the file. This method can work well, but it is not a good way. You can use an Excel VBA Macro to accomplish it quickly. And this post will show you how to use an Excel VBA code to save a range of selection to a text file.

1. Save a Range Selection as Text File

You can use an Excel VBA macro to save the selection as a text file without copying and pasting to a new file. Just do the following steps:

Step1: open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Step2: then the “Visual Basic Editor” window will appear.

Step3: click “Insert” ->”Module” to create a new module.

Step4: paste the below VBA code into the code window. Then clicking “Save” button.

Sub SaveSelectionAsTextFile()
    Dim myFolder As String
    'Save Range as Text File.
    Set myRange = Application.Selection
    Set myRange = Application.InputBox("Select one Range to be copied", "SaveSelectionAsTextFile", myRange.Address, Type:=8)
    ActiveSheet.Activate
    myRange.Copy
    'This temporarily adds a sheet named "Test."
    Sheets.Add.Name = "Test"
    Sheets("Test").Select
    ActiveSheet.Paste
    'Ask user for folder to save text file to.
    myFolder = Application.GetSaveAsFilename(fileFilter:="Text Files (*.txt), *.txt")
    'Save selected data as text file in users selected folder.
    ActiveWorkbook.SaveAs Filename:=myFolder, FileFormat:=xlText, CreateBackup:=False
    'Remove temporary sheet.
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    ActiveWindow.SelectedSheets.Delete
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    'Indicate save action.
    MsgBox "Text File: " & myFolder & "Saved!"
    'Go to top of sheet.
    Range("A1").Select
End Sub

Step5: back to the current worksheet, then run the above excel macro. Click Run button.

Step6: Select one range to be copied. Click OK button.

Step7: enter one filename in File name text box in the Save As dialog box. Click Save button.

Step8: open the newly created text file test1112.txt in your local disk.

2. Video: Save a Range Selection to Text File in Excel

This video will demonstrate how to use VBA code to save a range selection to a text file in Excel.

In our daily work we may mark some texts in bold to make them to be noticed clearly. And if we want to extract them from original list, we need to know the ways to find them and then move them to another range. This free tutorial will introduce you two ways to extract bold texts, the first one is to extract bold texts via Find and Replace function, the second one is to edit VBA Macro.

Precondition:

See screenshot below. Some texts are marked in bold.

1. Extract Bold Text via Find and Replace Function

Step1: Press Ctrl+F to trigger Find and Replace dialog.

Step2: Click ‘Options>>’ button to get more settings, then click Format.

Step3: On Find Format window, click Font tab, under Font style select Bold, then click OK.

Step4: After clicking OK, go back to Find and Replace dialog, click Find All to load all matched values. All bold texts are displayed, you can drag the scrollbar to check all of them.

Step5: Press Ctrl+A on one of the loaded results, then all values are selected.

At the same time, bold text cells are also selected on original table.

Step6: Close Find and Replace dialog. Then you can copy them and paste them to another range.

2. Extract Bold Text via VBA Macro

Step1: On current visible worksheet, right click on sheet name tab to load Sheet management menu. Select View Code, Microsoft Visual Basic for Applications window pops up.

Or you can enter Microsoft Visual Basic for Applications window via Developer->Visual Basic. You can also press Alt + F11 keys simultaneously to open it.

Step2: In Microsoft Visual Basic for Applications window, click Insert->Module, enter below code in Module1. In this step we define a new function ‘ExtractBold’.

Function ExtractBold(SelectRng As Range)
    If SelectRng.Font.Bold Then
        ExtractBold = SelectRng.Value
    Else
        ExtractBold = ""
    End If
End Function

Step3: Save the macro. And then quit Microsoft Visual Basic for Applications.

Step4: In C2 apply the new function ‘ExtractBold’ we just created . Enter formula =ExtractBold(A2).

Step5: Press Enter and verify that nothing is returned, that’s because ‘NPS001’ is not in bold.

Step6: Drag the fill handle down to the end. Verify that bold texts in original table are extracted. But they are not displayed in bold after extracting.

Step7: Apply ExtractBold function in D column to extract bold texts from column B. Now all bold texts are extracted. You can select all of them and mark them in bold after extracting them per your demand.

3. Video: Extract Bold Text from A List in Excel

This video will show you how to extract bold text from a list in Excel using both the Find and Replace feature and VBA code.

This post will guide you how to hide all objects in your worksheet in Excel. How do I show all hidden objects in your current worksheets with VBA Macro in Excel.

If you want to hide all objects in your current worksheet, such as: pictures, comments, charts, Textbox, etc. You can use Selection pane feature or write down an Excel VBA Macro to show or hide all objects. Let’s see the following introduction.

1. Hide or Show All Objects with Selection Pane Feature

If you want to hide or unhide all objects in the current worksheet, you can use the Selection pane feature to achieve the result. Just do the following steps:

Step1: go to HOME tab, click Find & Select command under Editing group. And select Selection Pane … menu from the drop down menu list. And the Selection pane will be shown.

Step2: you should see that all of the objects are listed in the Selection pane. And You can click Hide All button to hide all objects. Or you can click Show All button to show all hidden objects.

Step3: if you only want to hide or show specific objects in Excel, you just need to click its eye button, or click eye button again to show it.

2. Hide or Show All Objects with VBA

You can also use an Excel VBA macro to achieve the same result of hiding or showing all objects in the current worksheet. Here are the steps:

Step1: open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Step2: then the “Visual Basic Editor” window will appear.

Step3: click “Insert” ->”Module” to create a new module.

Step4: paste the below VBA code  into the code window. Then clicking “Save” button.

Sub HideAllObjects()
    Dim ob As Shape
    For Each ob In ActiveSheet.Shapes
        ob.Visible = False
    Next
End Sub

Step5: back to the current worksheet, then run the above excel macro. Click Run button.

All objects in the current worksheet would be hidden.

If you want to show all hidden objects with VBA code, you can use the following VBA Macro:

Sub ShowAllObjects()
    Dim ob As Shape
    For Each ob In ActiveSheet.Shapes
        ob.Visible = True
    Next
End Sub

3. Video: How to Hide or Show Objects in Excel

In this video, you will learn how to hide or show objects in Excel using the selection pane feature as well as VBA code.

This post will guide you how to open multiple links at once in Excel. How do I open multiple Hyperlinks with VBA Macro in Excel 2013/2016/2019/365.

 When dealing with large data sets, it can be time-consuming and frustrating to manually click on each hyperlink to access its contents. Excel provides several built-in features to simplify this process, such as the “Follow Hyperlink” function. However, this function only works for one hyperlink at a time. In this post, we will explain how to use VBA code to automate the process of opening multiple hyperlinks quickly in Excel.

1. Open Multiple Links at Once with VBA Code

Assuming that you have a list of data which contain multiple hyperlinks and you want to open those links at once in your current worksheet. How to do it quickly. I think you have to use the VBA Macro to quickly achieve it. Just do the following steps:

Step1: open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Step2: then the “Visual Basic Editor” window will appear.

Step3: click “Insert” ->”Module” to create a new module.

Step4: paste the below VBA code  into the code window. Then clicking “Save” button.

Sub OpenMultipleLinks()
    On Error Resume Next
    Set myRange = Application.Selection
    Set myRange = Application.InputBox("Range", "OpenMultipleLinks", myRange.Address, Type:=8)
    For Each oneLink In myRange.Hyperlinks
        oneLink.Follow
    Next
End Sub

Step5: back to the current worksheet, then run the above excel macro. Click Run button.

Step6: please select one range that contain hyperlinks you want to open. click ok button.

Step7: you would see that all of hyperlinks have been opened in your default browser.

2. Video: Open Multiple Hyperlinks at Once with VBA Code

This video will demonstrate a quick and easy way to open multiple hyperlinks simultaneously in Excel using VBA code, saving you time and effort while increasing productivity.

This post will guide you how to count the number of sheets in a workbook in Excel 2013/2016/2019/365. How do I count the number of worksheets in a workbook with VBA Macro in Excel.

While manually counting the sheets is possible, it can be a tedious and time-consuming task, especially in larger workbooks. Fortunately, Excel provides several built-in functions and features that can help simplify this task. Additionally, for those who are comfortable with using VBA Macros, we will also explore a quick and easy method to count the number of sheets using a simple VBA macro.

1. Count the Number of Sheets with Define Name

If you want to count the number of worksheets in a given workbook in Excel, you can use the Defined Name and a Formula to achieve it. Just do the following steps:

 Step1: go to Formula tab, click Define Name command under Defined Names group, and the New Name dialog will open.

 Step1: type one defined name in the Name text box, such as: countWorksheets, and then type the formula =GET.WORKBOOK(1)&T(NOW()) into the text box of Refers to. Click Ok button.

 Step3: Type the following formula based on the COUNTA function and the INDEX function to get the number of worksheets in the current workbook. And press Enter key in your keyboard, you will get the number of worksheets in your workbook.

=COUNTA(INDEX(CountWorksheets,0))

2. Count the Number of Sheets with VBA Macro

You can also use an Excel VBA Macro to get the number of worksheets in the current workbook. Just do the following steps:

 Step1: open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

 Step2: then the “Visual Basic Editor” window will appear.

 Step3: click “Insert” ->”Module” to create a new module.

 Step4: paste the below VBA code into the code window. Then clicking “Save” button.

Sub CountWorkSheets()
    MsgBox "The number of worksheets in the current workbook is : " & Application.Sheets.Count
End Sub

 Step5: back to the current worksheet, then run the above excel macro. Click Run button.

 Step6: let’s see the result:

3. Video: Count the Number of Sheets

This video will guide you through a step-by-step process to count the number of sheets in an Excel workbook using VBA code, saving you time and effort while ensuring accuracy.

This post will provide you with a step-by-step guide on how to save Excel workbook file with Cell value in Excel using VBA code in Excel 2013/2016/2019/365. Saving files based on specific criteria can be a tedious and time-consuming task, especially if you have to do it repeatedly. However, with VBA code, you can automate this process and save yourself a lot of time and effort.

1. Save File Based on Cell Value with VBA Code

Assuming that you have a request that save the current workbook file I am working as the value in Cell A1. For example, the value of Cell A1 is testWork, and you want to save the workbook as the name of testWork.

To Save file with Cell Value in Excel, you need to write down an Excel VBA Macro to achieve the result. Just do the following steps:

Step1: open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Step2: then the “Visual Basic Editor” window will appear.

Step3: click “Insert” ->”Module” to create a new module.

Step4: paste the below VBA code  into the code window. Then clicking “Save” button.

Sub FileNameAsCellContent()
    Dim FileName As String
    Dim Path As String
    Application.DisplayAlerts = False
    Path = "C:\test\"
    FileName = Range("A1").Value & ".xlsx"
    ActiveWorkbook.SaveAs Path & FileName, xlOpenXMLWorkbook
    Application.DisplayAlerts = True
    ActiveWorkbook.Close
End Sub

Step5: back to the current worksheet, then run the above excel macro. Click Run button.

Step6: Let’s see the result:

2. Video: Save File based on Cell Value in Excel

This video will demonstrate how to save a file based on a cell value in Excel using VBA code.

3. Conclusion

Now you should have a good understanding of how to use VBA code to save files based on cell values, and you’ll be able to apply this knowledge to your own Excel projects.

This post will guide you how to insert the contents of a paricular into the header or footer cell in your active worksheet in Excel. How do I put a cell value into header or footer in all worksheets in your active workbook using VBA Macro in Excel.

You can easily add the number of pages, the current date and time or the name of current file, including the File Full path into the header or footer in your Excel. And there is no built-in command or function to add a cell value into the header or footer. How to do it. You can use an Excel VBA Macro to insert cell Content into a paricular worksheet or all worksheet in your workbook quickly.

1. Insert cell Content into Header or Footer in a Worksheet

To put a cell value into the header or footer in your current worksheet in Excel, and you can do the following steps:

Step1: open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Step2: then the “Visual Basic Editor” window will appear.

Step3: click “Insert” ->”Module” to create a new module.

Step4: paste the below VBA code  into the code window. Then clicking “Save” button.

Sub InsertCellValueIntoHeader()
    Dim myRange As Range
    Set myRange = Application.Selection
    Set myRange = Application.InputBox("Select One Single Cell that you want to put its into Header or Footer", "InsertCellValueIntoHeader", myRange.Address, Type:=8)
    Application.ActiveSheet.PageSetup.LeftHeader = myRange.Value
End Sub

Step5: back to the current worksheet, click on Macros button under Code group. then click Run button.

Step6: Select One Single Cell that you want to put its into Header or Footer. click on Ok button.

Step7: let’s see the last result:

If you want to add a cell value into the footer in your current worksheet, and you can use the following VBA Macro.

Sub InsertCellValueIntoFooter()
    Dim myRange As Range
    Set myRange = Application.Selection
    Set myRange = Application.InputBox("Select One Single Cell that you want to put its into Header or Footer", "InsertCellValueIntoHeader", myRange.Address, Type:=8)
    Application.ActiveSheet.PageSetup.LeftFooter = myRange.Value
End Sub

If you need to add a cell value into the RightHeader or RightFooter, and you just need to change one code line(Application.ActiveSheet.PageSetup.LeftHeader or Application.ActiveSheet.PageSetup.LeftFooter ) in above VBA Function as below line:

Application.ActiveSheet.PageSetup.RigthHeader = myRange.Value

or

Application.ActiveSheet.PageSetup.RightFooter = myRange.Value

2. Insert Cell Content into Header or Footer in All Worksheets

You can also insert a cell value into the header or footer for all worksheets in your active workbook using a VBA Macro in Excel.  see belows:

Sub InsertCellValueIntoHeaderForAllSheets()
    Dim myRange As Range
    Set myRange = Application.Selection
    Set myRange = Application.InputBox("Select One Single Cell that you want to put its into Header or Footer", "InsertCellValueIntoHeader", myRange.Address, Type:=8)
    For Each mysheet In Application.ActiveWorkbook.Worksheets
        mysheet.PageSetup.LeftHeader = myRange.Value
    Next
End Sub

If you need to insert cell content into footer for all worksheets, and you can use the following VBA Macro:

Sub InsertCellValueIntoFooterForAllSheets()
    Dim myRange As Range
    Set myRange = Application.Selection
    Set myRange = Application.InputBox("Select One Single Cell that you want to put its into Header or Footer", "InsertCellValueIntoHeader", myRange.Address, Type:=8)
    For Each mysheet In Application.ActiveWorkbook.Worksheets
        mysheet.PageSetup.LeftFooter = myRange.Value
    Next
End Sub

3. Video: Insert Cell Content into Header or Footer in Excel

If you want to learn how to insert cell content into header or footer in Excel, this video will show you a simple and effective way using VBA code.

This post will guide you how to save each worksheet to separate CSV files in your workbook in Excel. How do I save multiple or all worksheets to separate csv file in Excel 2013/2016. How to export all worksheets to separate txt files with VBA macro in Excel.

1. Export Each Sheet to Separate CSV Files

Assuming that you have a workbook that has four different worksheets, sheet1, sheet2, sheet3, and sheet4. And You want each worksheet as its own CSV file and we also want the CSV file name to take into account its Excel Source as well as the workbook where it originates. To achieve the result of exporting multiple worksheets separate csv files, you have to use VBA Macro to save all worksheets in the current workbook to separated csv files. Here are the steps:

Step1: open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Step2: then the “Visual Basic Editor” window will appear.

Step3: click “Insert” ->”Module” to create a new module.

Step4: paste the below VBA code  into the code window. Then clicking “Save” button.

Public Sub SaveWorksheetsAsCsv()

Dim WS As Excel.Worksheet
Dim SaveToDirectory As String

Dim CurrentWorkbook As String
Dim CurrentFormat As Long

 CurrentWorkbook = ThisWorkbook.FullName
 CurrentFormat = ThisWorkbook.FileFormat
' Store current details for the workbook

      SaveToDirectory = "D:\tmp\"

      For Each WS In ThisWorkbook.Worksheets
          WS.SaveAs SaveToDirectory & WS.Name, xlCSV
      Next

 Application.DisplayAlerts = False
  ThisWorkbook.SaveAs Filename:=CurrentWorkbook, FileFormat:=CurrentFormat
 Application.DisplayAlerts = True
' Temporarily turn alerts off to prevent the user being prompted
'  about overwriting the original file.

End Sub

Step5: back to the current worksheet, then run the above excel macro. Click Run button.

Step6: you would see that all worksheet has been converted to a single CSV files in the specified directory. This example will save csv file into D:\tmp\ directory. You need to change the variable “SaveToDirectory ” as you need. or you can also create a temp directory called tmp in disk D partition.

2. Export Each Sheet to Separate Text Files

If you want only convert each worksheet to separate text files for your active workbook, you can also use an Excel VBA Macro to achieve the result. Just using the following VBA code:

Public Sub SaveWorksheetsAsCsv()

Dim WS As Excel.Worksheet
Dim SaveToDirectory As String

Dim CurrentWorkbook As String
Dim CurrentFormat As Long

 CurrentWorkbook = ThisWorkbook.FullName
 CurrentFormat = ThisWorkbook.FileFormat
' Store current details for the workbook

      SaveToDirectory = "D:\tmp\"

      For Each WS In ThisWorkbook.Worksheets
          WS.SaveAs SaveToDirectory & WS.Name & ".txt"
      Next

 Application.DisplayAlerts = False
  ThisWorkbook.SaveAs Filename:=CurrentWorkbook, FileFormat:=xlText
 Application.DisplayAlerts = True
' Temporarily turn alerts off to prevent the user being prompted
'  about overwriting the original file.

End Sub

Let’s see the result:

3. Conclusion

By using VBA code, you can create a custom macro that will iterate through each sheet in your workbook and save it as a separate CSV file. This code can be customized to suit your specific needs and can be easily modified to include additional functionality or to save the files in a different format.

Overall, if you frequently work with large Excel workbooks that contain multiple sheets, using VBA code to save each sheet as a separate CSV file is a great way to streamline your workflow and improve your productivity.

This post will guide you how to deselect cells from a selected range of cells in your worksheet. How do I quickly deselect cells from a selected range of cells with VBA code in Excel.

Normally, if you want to deselect a cell or multiple cells from a selection, you just need to hold down the CTRL key and click on the cells you want to deselect. If you want to unselect a range of selected cells, you need to hold down the CTRL key and drag the range you want to deselect. This post will explain that how to deselect cells from a selection with VBA Macro quickly in Excel.

1. Deselect Cells from Selected Range with VBA

You can use an Excel VBA Macro to quickly achieve the same result of deselecting one or multiple cells from a selected range of cells. Just do the following steps:

Step1: open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Step2: then the “Visual Basic Editor” window will appear.

Step3: click “Insert” ->”Module” to create a new module.

Step4: paste the below VBA code into the code window. Then clicking “Save” button.

Sub DeSelectCells()
    Set myRange = Application.Selection
    Set myRange = Application.InputBox("Select one Range:", "DeSelectCells", myRange.Address, Type:=8)
    Set DeleteRng = Application.InputBox("select one cell or range of cells that you want to deselect", "DeSelectCells", Type:=8)
    Dim LastRange As Range
    
    For Each myCell In myRange
        If Application.Intersect(myCell, DeleteRng) Is Nothing Then
            If LastRange Is Nothing Then
                Set LastRange = myCell
            Else
                Set LastRange = Application.Union(LastRange, myCell)
            End If
        End If
    Next

    LastRange.Select

End Sub

Step5: back to the current worksheet, then run the above excel macro. Click Run button.

Step6: Select one Range that you want to deselect cells, such s:A1:D7

Step7: select one cell or range of cells that you want to deselect.

Step8: Let’s see the last result:

2. Video: Deselect Cells from Selected Range with VBA

This video will demonstrate you how to deselect cells from a selected range of cells with a VBA Macro in your worksheet.

This post will guide you how to select only bold cells in a range of cells in Excel. How do I select all bold cells in a given range using VBA Macro in Microsoft Excel 2013/2016/2019/365.

Assuming that you have a list of data in range B1:C5, and you want to select all cells with bold font formatting in Excel. This post will show you two methods to select all bold cells.

1. Select Only Bold Cells Using Find And Replace

If you want to select all cells with bold fond formatting, you can use Find and Replace feature to find the specific text or specific format in the worksheet, such as: cell color, bold font…etc. Just do the following steps to find and select all the bold formatting in the selected range of cells:

Step1: select the range in which contain bold cells that you want to find.

Step2: go to Home tab, click Find & Select command under Editing group. And click on Find from the Find and Select drop down list. The Find and Replace dialog will open.

Step3: click on the Options button in the Find and Replace dialog box.

Step4: click the Format button in the Find what section, and select Choose Format From Cell from the Format drop down list. And the Find Format dialog will open.

Step5: click Font tab in the Find Format dialog box, and select the Bold option in the Font Style list box, and click Ok button to go back to the Find and Replace dialog box.

Step6: click the Find All button, then all cells with the bold font formatting in the selected range of cells would be searched.

Step7: press Ctrl + C keys on your keyboard to select all results, then it will select all the cells that have the text with bold fond formatting.

2. Select Only Bold Cells Using VBA

You can also use an Excel VBA Macro to achieve the same result of selecting only bold cells in a given range of cell. Just do the following steps:

Step1: open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Step2: then the “Visual Basic Editor” window will appear.

Step3: click “Insert” ->”Module” to create a new module.

Step4: paste the below VBA code  into the code window. Then clicking “Save” button.

Sub SelectOnlyBoldCells()
    Dim myRange As Range
    Dim cell As Range
    Dim tempRange As Range
    
    Set myRange = Range("A1", "D3")
    For Each cell In myRange
        If cell.Font.Bold = True Then
            If tempRange Is Nothing Then
                Set tempRange = cell
            Else
                Set tempRange = Union(tempRange, cell)
            End If
        End If
    Next cell
     
    If Not tempRange Is Nothing Then
        tempRange.Select
    End If
    
End Sub

Note: This VBA code will use another range called tempRange, which receives the cell reference with the bold fond formatting. And the UNION function is used to concatenate the addresses together. After the procedure finished, the range tempRange is selected.

You need to change the value of myRange variable as you need.

Step5: back to the current worksheet, then run the above excel macro. Click Run button.

Step6: Let’s see the result:

3. Video: How to Select Only Bold Cells in Excel

In this video, you will learn how to select only bold cells in Excel using find and replace as well as VBA code.

This post will guide you on how to set specific cells as read-only or locked using Protect Sheet feature and vba code in Microsoft Excel 2013/2016/2019/365.

In our daily work, in case of protect our worksheet from being edit by others, we often add a password for opening worksheet. But sometimes, the worksheet is open for all or shared with your partners, we just want to set some important cells as read only in case of preventing others to edit them in worksheet, for example we need to set some cells with formulas or salaries as read only in payroll, how can we do? This article will help you to make your specific cells as read only.

1. Lock Specific Cells in Worksheet by Format Cells Settings

Prepare a table with ID, Name, Score three columns. And we want to make some cells in this table cannot be editable by others.

A simple table for demonstration in this article:

Step1: Press Ctrl+A to select the whole worksheet. You can also click on the arrow to select whole cells on current worksheet, see screenshot below.

Step2: On worksheet anywhere, right click to load menu, select Format Cells. You can also load Format Cells window by press Ctrl+1.

Step3: Format Cells window is loaded properly.

Step4: Click on Protection, there are two options, Locked and Hidden, Locked option is checked by default. Uncheck Locked option, then click OK.

Notes:

a. You can see the comments under the option that locked or hidden options only take effective once protect the worksheet is enabled. So, we need to protect the worksheet then.

b. If we don’t uncheck Locked option first, and directly protect worksheet by password, then the whole worksheet will be locked. So this step is to make sure that only the selected range will be locked, please see the following step.

Step5: Select the range you want to make them as Read Only, for example select A2:C4. Right click to load menu, select Format Cells again.

Step6: This time, enter Protection tab, check on the Locked option, then click OK.

Step7: Click Review->Protect Sheet.

Step8: In Protect Sheet window, “Protect worksheet and contents of locked cells”, “Select locked cells” and “Select unlocked cells” are checked by default. Enter password in textbox and click OK, re-enter and confirm password again, then click OK.

Enter password:

Confirm password:

Step9: Try to edit the table. Verify that below error message pops up.

Step10: Click A3. Verify that user still can edit it due to it is not locked.

Step11: If we want edit the locked cells, please click on Review->Unprotect Sheet. Unprotect Sheet pops up.

Step12: Enter the password. Click OK. Now you can edit specific locked cells normally again.

2. Lock Specific Cells in Worksheet by VBA

Step1: Right click on Sheet1 to load Sheet management menu. Select View Code, Microsoft Visual Basic for Applications window pops up.

Or you can enter Microsoft Visual Basic for Applications window via Developer->Visual Basic.

Step2: In Microsoft Visual Basic for Applications window, click on Sheet1, enter below code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column = 1 Or Target.Column = 2 Or Target.Column = 3 Then
        If Target.Row = 2 Or Target.Row = 3 Or Target.Row = 4 Then
            Beep
            Cells(Target.Row, Target.Column).Offset(0, 1).Select
            MsgBox Cells(Target.Row, Target.Column).Address & " You cannot edit the cell!", _vbInformation, "Error Message"
        End If
    End If
End Sub

Comments:

a. In above script, Target.Column and Target.Row define the range will be locked. In this case, column 1,2,3 (A, B, C) and row 2,3,4 are included, that means range A2:C4 is selected and locked.

b. “You cannot edit the cell!” is the message pops up when user clicking on locked cell. “Error Message” is the header for pops up error message. You can replace them.

Step3: Save the codes, see screenshot below. And then quit Microsoft Visual Basic for Applications.

Step4: Try to edit the locked range A2:C4. Below error message pops up.

Step5: Try to edit A1 which is not locked. Verify that user still can edit A1 properly. User can also edit other unlocked cells as well.

3. Video: Set Specific Cells as Read Only/Locked in Excel

This Video will show yo how to set specific cells as red-only or locked in Excel using Protect Sheet feature and VBA Code.

4. SAMPLE Files

Below are sample files in Microsoft Excel that you can download for reference if you wish.

5. Conclusion

Excel provides users with the ability to protect their worksheets and workbooks to prevent unauthorized changes. By default, all cells in a worksheet are editable, but you can restrict access to specific cells to maintain data integrity and prevent accidental changes. In this guide, you can set specific cells as read-only or locked, and ensure that only authorized users can modify them.

This post will guide you how to center an image file in an excel cell. And how do I center to align a graphic in an excel cell. How to align a picture within a cell. How to align a picture to the center of a cell with VBA code in excel.

1. Center an Image in an Excel Cell

To quickly center an image in an excel cell, you can create an excel VBA macro to achieve the result. Just do the following steps:

Step1: import one picture into the current worksheet.

Step2: click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Step3: then the “Visual Basic Editor” window will appear.

Step4: click “Insert” ->”Module” to create a new module

Step5: paste the below VBA code into the code window. Then clicking “Save” button.

Sub CenterImages()
     With ActiveSheet.Shapes("Picture 1")
         .Top = Range("B1").Top + (Range("B1").Height - .Height) / 2
         .Left = Range("B1").Left + (Range("B1").Width - .Width) / 2
    End With
End Sub

Step6: back to the current worksheet, then run the above excel macro. Click Run button.

Step7: Let’s see the result.

2. Video: Center an Image in an Excel Cell

This video will show you how to center an image in an Excel cell using VBA code.

3. Conclusion

Centering an image in an Excel cell can enhance the presentation of the data and make the spreadsheet more visually appealing, and can be achieved through various methods, including manual adjustment or by using VBA code to automate the process.

If you’re working with large workbooks in Excel, you may find it useful to know the size of each worksheet within the workbook. This information can help you identify which worksheets are taking up the most space and optimize your file for better performance.

This post will guide you how to check the size of each worksheet of workbook using VBA code in Excel. How do I get the data size of each worksheet in a workbook in Excel 2013/2016/2019/365.

Assuming that you have a Workbook which contains multiple worksheet, and you want to know the data size of each worksheet in the current workbook. This post will show two methods with you.

Method1: Get the Data Size of Each Worksheet by Manually

You can get the data size of each worksheet one by one manually, and you just need to copy the contents of an Excel worksheet in your workbook, and then create a new workbook, and pasted the content into sheet1 in the new workbook, and then save the new workbook to a windows folder. Then you can get the size of this newly workbook.

You can repeat the above steps to get the data sizes of other worksheets one by one in your workbook.

Method2: Get the Data Size of Each Worksheet via VBA Macro

If you want to quickly get the data size of each worksheet in your current workbook, you can use an Excel VBA macro to accomplish it. Just do the following steps:

Step1: open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Step2: then the “Visual Basic Editor” window will appear.

Step3: click “Insert” ->”Module” to create a new module.

Step4: paste the below VBA code  into the code window. Then clicking “Save” button.

Sub WorksheetSizes_excelhow()
    Dim wks As Worksheet
    Dim c As Range
    Dim sFullFile As String
    Dim sReport As String
    Dim sWBName As String

    sReport = "Size Report"
    sWBName = "tmp.xls"
    sFullFile = ThisWorkbook.Path & _
      Application.PathSeparator & sWBName

    ' Add new worksheet to record sizes
    On Error Resume Next
    Set wks = Worksheets(sReport)
    If wks Is Nothing Then
        With ThisWorkbook.Worksheets.Add(Before:=Worksheets(1))
            .Name = sReport
            .Range("A1").Value = "Worksheet Name"
            .Range("B1").Value = "Approximate Size"
        End With
    End If
    On Error GoTo 0
    With ThisWorkbook.Worksheets(sReport)
        .Select
        .Range("A1").CurrentRegion.Offset(1, 0).ClearContents
        Set c = .Range("A2")
    End With

    Application.ScreenUpdating = False
    ' Loop through worksheets
    For Each wks In ActiveWorkbook.Worksheets
        If wks.Name <> sReport Then
            wks.Copy
            Application.DisplayAlerts = False
            ActiveWorkbook.SaveAs sFullFile
            ActiveWorkbook.Close SaveChanges:=False
            Application.DisplayAlerts = True
            c.Offset(0, 0).Value = wks.Name
            c.Offset(0, 1).Value = FileLen(sFullFile)
            Set c = c.Offset(1, 0)
            Kill sFullFile
        End If
    Next wks
    Application.ScreenUpdating = True
End Sub

Step5: back to the current worksheet, then run the above excel macro. Click Run button.

Step6: let’s see the last result:

Video: Get the Data Size of Each Worksheet

This video will show you how to use VBA code to get the data size of each worksheet in a workbook.

Conclusion

With the above VBA code, you can quickly and easily retrieve the size of each worksheet in your workbook and take necessary steps to manage the file size in Microsoft Excel.