Musiha Mahfuza Mukta

About author

Musiha Mahfuza Mukta is a marine engineer who loves exploring Excel and VBA programming. To her, programming is a time-saving tool for dealing with data, files, and the internet. She's skilled in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. With a B.Sc in Naval Architecture & Marine Engineering from BUET, she's shifted gears and now works as a content developer. In this role, she creates techy content exclusively focused on Excel and VBA. Beyond the tech stuff, catch her lost in books, explore new places during travels, and enjoy movies and TV series in her downtime.

Designation

Excel & VBA Content Developer at ExcelDemy in SOFTEKO.

Lives in

Dhaka, Bangladesh.

Education

B.Sc in Naval Architecture & Marine Engineering, BUET.

Expertise

Rhino3D, Maxsurf, C++, MS Office, AutoCAD, and Excel & VBA

Experience

  • Technical Content Writing
  • Team Management

Research & Publication

Latest Posts From Musiha Mahfuza Mukta

0
How to Use Excel VBA Border Weight Property (8 Examples)

When you have a large dataset and want to change the border thickness or even when you want to put a border within your dataset, you can use the Excel ...

0
How to Use Implicit Intersection Operator in Excel (4 Examples)

Sometimes you may need to use an implicit intersection operator in Excel. In this article, I will show you how, and where to use an implicit intersection (@) ...

0
How to Use IF-THEN Statements with Text in Excel (7 Examples)

Sometimes you may need to use some combination of functions in Excel for searching a particular text or returning the answer as text. So, if you are looking ...

0
How to Make Floating Bar Chart in Excel (2 Easy Ways)

Sometimes for data visualization, you may need to create a floating bar chart in Excel. So, if you are looking for how to make this chart in Excel, then you ...

0
How to Shade Area Between Two Lines in a Chart in Excel 

To increase the visuality of a data range, you may need to shade the area between two lines of an Excel chart. So, if you are looking for how to shade the area ...

0
How to Perform Drawing of Objects with VBA in Excel (2 Examples)

Sometimes you may need to draw a shape or an object in Excel. So, if you are looking for how to perform drawing of objects using VBA code in Excel, then you ...

0
How to Calculate Volatility for Black Scholes in Excel (2 Methods)

Sometimes you may need to calculate volatility using the Black-Scholes formula in Excel. So, if you are looking for how to calculate volatility for the ...

0
How to Create Project Pipeline in Excel (2 Suitable Methods)

Sometimes for business or institute purposes, you may need to create a project pipeline in Excel to track all your projects easily. So, if you are looking for ...

0
How to Find Intersection of Two Trend Lines in Excel (3 Methods)

An intercept of two trendlines represents the solution for these equations and you may need to find that point using Excel. So, if you are looking for how to ...

0
How to Create Chart Slider in Excel (2 Suitable Ways)

Sometimes for data visualization, you may need to make a chart. Furthermore, a chart slider is a more interesting and useful thing for data visualization. You ...

0
How to Calculate Skewness in Excel (2 Effective Methods)

Skewness is a basic statistical concept. Sometimes, you may need to calculate the skewness in Excel. So, if you are looking for how to calculate the skewness ...

0
How to Do Multi Product Break Even Analysis in Excel

If you are heading to a new business, then you should know the calculation of the multi-product break-even analysis. So, if you are looking for how to do ...

0
How to Find Intercept of Two Lines in Excel (3 Suitable Ways)

An intercept of two lines represents the solution for these equations and you may need to find that point using Excel. So, if you are looking for how to find ...

0
[Fixed!] Excel MOD Function Not Working (3 Issues with Solutions)

Sometimes you may need to use the MOD function in Excel. Eventually, the MOD function may not be working in Excel. In that case, if you are looking for the ...

0
How to Truncate Text from Left in Excel (5 Suitable Ways)

When you have a big dataset with text as cell values, then you may need to shorten the text in Excel. Basically, you can truncate a text either from left, from ...

Browsing All Comments By: Musiha Mahfuza Mukta
  1. Reply Avatar photo
    Musiha Mahfuza Mukta Nov 15, 2023 at 12:24 PM

    Hello Daphne, the VBA code is perfectly working on my laptop. The code is fine. To run a VBA code you must follow:
    1. Save your Excel file in .xlsm format
    2. Use the Excel offline version
    3. Enable macro content, to do so right click on Excel file >> from the Context Menu Bar >> go to Properties option >> General >> Security >> Unblock
    Still, if you face the problem, then please go through this article [Fixed!] Macros Not Working in Excel. Hopefully, this article will help you to solve your issue.
    Regards
    Exceldemy Team

  2. Reply Avatar photo
    Musiha Mahfuza Mukta Oct 22, 2023 at 12:10 PM

    Thank you so much for pointing out this issue, Debbie! I wholeheartedly express my gratitude for the valuable time you dedicated to sharing your expertise and aiding others in addressing this matter.
    I have added the limitations of Excel (regarding date format) in the article too. Thanks again!
    Regards,
    Musiha|Exceldemy

  3. Reply Avatar photo
    Musiha Mahfuza Mukta Oct 8, 2023 at 12:27 PM

    Hello, Sudhir, Thanks for your comment. You need to import external source data for Parish/County information. You can use the following link: u.s. census bureau’s website to get those information.
    Also, this site provides the Excel file. So, you can download that file and use this as the source file.

    Or, you can copy your needed data from this site and then paste these in your Excel file.
    Regards
    Musiha

  4. Reply Avatar photo
    Musiha Mahfuza Mukta Oct 3, 2023 at 12:24 PM

    Thanks, IFN, for your comment. Here, the entire article explains how to create a Date Picker. But to get the drop-down menu, you should use more steps. So, after the completion of Date Picker, use the following steps.
    Step1: Select the cell where you want to keep the drop-down list >> from Data tab >> Data Tools group >> Data Validation >> Data Validation >> in the dialog box, go to Settings >> List >> Select range (the listed items including “Calendar” word) in the Source box >> press OK. As a result, you will get a drop-down list in that selected cell.

    Step2: In the UserForm1, drag a new Command Button >> double click on Command Button >> write the following Code.

    Private Sub CommandButton1_Click()
    Cells(10, 2) = UserForm1.CsnDate.Caption
    End Sub

    Use your preferred cell reference.

    Step3: In B2 cell >> use this formula >> “=B3

    Step4: In VB Editor, from Insert tab >> Module >> write the following code in Module1.

    Sub running()
    If Range("A1").Cells(2, 2).Value = "Calendar" Then
    UserForm1.Show
    End If
    End Sub


    Step5: Go to Worksheet >> right click on sheet name >> from Context Menu Bar >>View Code >> write the following code.

    Private Sub Worksheet_Change(ByVal Target As Range)
    Set Target = Range("B2")
    If Target.Value = "Calendar" Then
    Call running
    End If
    End Sub


    Step6: Now save the code >> go back to sheet >> check the output. When you choose “Calendar” from the dropdown list >> you will get the date picker visible >> choose your date >> press Command Button1 >> get the chosen date in B10 cell. If you get any error notice, then just press “End” to that notice.

    When you selected the “Calendar” word, then with every change doing in your worksheet, you will get the date picker. So, if you need to write any other things, then first change the “Calendar” word from the drop down list.

  5. Reply Avatar photo
    Musiha Mahfuza Mukta Oct 3, 2023 at 11:46 AM

    Hello Orly, to fill series with the middle number, you should use a formula. In A1 cell, keep the initial value. Then go to A2 cell >> use this formula–> =”Unit-305/”&TEXT(1+ROWS($A$1:A1),”000″)&”/2023″ >> press Enter. After that, drag the Fill Handle icon up to the last cell.

    Formula Breakdown
    After Equal Sign, write the first fixed part of your value within an Inverted Comma (“Unit-305/”). Then, use Ampersand Operator to join the formula. Here, inside the TEXT function use summation for the increment of middle number. Also, the TEXT function will consider the mentioned pattern (“001”).
    Now, again give the Ampersand Operator, and within another Inverted Comma keep the last part of the value (“/2023”).
    Regards
    Musiha|Exceldemy

  6. Reply Avatar photo
    Musiha Mahfuza Mukta Sep 19, 2023 at 11:38 AM

    Hey Anita,
    Sorry for the issues you are facing. If these methods don’t work, you can break the link to disable the updates. To break links, go to the Data tab >> select Edit Links >> select the link >> click on Break Link.

    I hope this will help you solve the problem. Here, you can try some more options like changing the name of the Source file. Also, you should change the location of the Source file. It will stop Excel from connecting the existing file with the Source file.
    Furthermore, if you don’t want to update your file, you can use the Copy-Paste(Value Only) feature for transferring data from a Source file.
    Regards
    Musiha|ExcelDemy

  7. Reply Avatar photo
    Musiha Mahfuza Mukta Sep 7, 2023 at 1:06 PM

    Thanks, Dennis, for your comment. You can use Format function of VBA to change the format. Below you can see I have changed the format of current date. The Format function will consider the expression and wanted format. Must use inverted commas for mentioning format.

    Me.TDate.Caption = Format(Date, "dd-mm-yyyy")


    Also, use this Format function in every command button to change the chosen date format.

    For your better understanding, I’m mentioning the updated VBA code for new format.

    Private Sub CommandButton1_Click()
    Cells(10, 2) = UserForm1.CsnDate.Caption
    End Sub
    Private Sub DBttn1_Click()
    Me.CsnDate.Caption = Format(DateSerial(Me.YrBox2, Me.MnthBox1.ListIndex + 1, Me.DBttn1.Caption), "dd-mm-yyyy")
    End Sub
    Private Sub DBttn2_Click()
    Me.CsnDate.Caption = Format(DateSerial(Me.YrBox2, Me.MnthBox1.ListIndex + 1, Me.DBttn2.Caption), "dd-mm-yyyy")
    End Sub
    Private Sub DBttn3_Click()
    Me.CsnDate.Caption = Format(DateSerial(Me.YrBox2, Me.MnthBox1.ListIndex + 1, Me.DBttn3.Caption), "dd-mm-yyyy")
    End Sub
    Private Sub DBttn4_Click()
    Me.CsnDate.Caption = Format(DateSerial(Me.YrBox2, Me.MnthBox1.ListIndex + 1, Me.DBttn4.Caption), "dd-mm-yyyy")
    End Sub
    Private Sub DBttn5_Click()
    Me.CsnDate.Caption = Format(DateSerial(Me.YrBox2, Me.MnthBox1.ListIndex + 1, Me.DBttn5.Caption), "dd-mm-yyyy")
    End Sub
    Private Sub DBttn6_Click()
    Me.CsnDate.Caption = Format(DateSerial(Me.YrBox2, Me.MnthBox1.ListIndex + 1, Me.DBttn6.Caption), "dd-mm-yyyy")
    End Sub
    Private Sub DBttn7_Click()
    Me.CsnDate.Caption = Format(DateSerial(Me.YrBox2, Me.MnthBox1.ListIndex + 1, Me.DBttn7.Caption), "dd-mm-yyyy")
    End Sub
    Private Sub DBttn8_Click()
    Me.CsnDate.Caption = Format(DateSerial(Me.YrBox2, Me.MnthBox1.ListIndex + 1, Me.DBttn8.Caption), "dd-mm-yyyy")
    End Sub
    Private Sub DBttn9_Click()
    Me.CsnDate.Caption = Format(DateSerial(Me.YrBox2, Me.MnthBox1.ListIndex + 1, Me.DBttn9.Caption), "dd-mm-yyyy")
    End Sub
    Private Sub DBttn10_Click()
    Me.CsnDate.Caption = Format(DateSerial(Me.YrBox2, Me.MnthBox1.ListIndex + 1, Me.DBttn10.Caption), "dd-mm-yyyy")
    End Sub
    Private Sub DBttn11_Click()
    Me.CsnDate.Caption = Format(DateSerial(Me.YrBox2, Me.MnthBox1.ListIndex + 1, Me.DBttn11.Caption), "dd-mm-yyyy")
    End Sub
    Private Sub DBttn12_Click()
    Me.CsnDate.Caption = Format(DateSerial(Me.YrBox2, Me.MnthBox1.ListIndex + 1, Me.DBttn12.Caption), "dd-mm-yyyy")
    End Sub
    Private Sub DBttn13_Click()
    Me.CsnDate.Caption = Format(DateSerial(Me.YrBox2, Me.MnthBox1.ListIndex + 1, Me.DBttn13.Caption), "dd-mm-yyyy")
    End Sub
    Private Sub DBttn14_Click()
    Me.CsnDate.Caption = Format(DateSerial(Me.YrBox2, Me.MnthBox1.ListIndex + 1, Me.DBttn14.Caption), "dd-mm-yyyy")
    End Sub
    Private Sub DBttn15_Click()
    Me.CsnDate.Caption = Format(DateSerial(Me.YrBox2, Me.MnthBox1.ListIndex + 1, Me.DBttn15.Caption), "dd-mm-yyyy")
    End Sub
    Private Sub DBttn16_Click()
    Me.CsnDate.Caption = Format(DateSerial(Me.YrBox2, Me.MnthBox1.ListIndex + 1, Me.DBttn16.Caption), "dd-mm-yyyy")
    End Sub
    Private Sub DBttn17_Click()
    Me.CsnDate.Caption = Format(DateSerial(Me.YrBox2, Me.MnthBox1.ListIndex + 1, Me.DBttn17.Caption), "dd-mm-yyyy")
    End Sub
    Private Sub DBttn18_Click()
    Me.CsnDate.Caption = Format(DateSerial(Me.YrBox2, Me.MnthBox1.ListIndex + 1, Me.DBttn18.Caption), "dd-mm-yyyy")
    End Sub
    Private Sub DBttn19_Click()
    Me.CsnDate.Caption = Format(DateSerial(Me.YrBox2, Me.MnthBox1.ListIndex + 1, Me.DBttn19.Caption), "dd-mm-yyyy")
    End Sub
    Private Sub DBttn20_Click()
    Me.CsnDate.Caption = Format(DateSerial(Me.YrBox2, Me.MnthBox1.ListIndex + 1, Me.DBttn20.Caption), "dd-mm-yyyy")
    End Sub
    Private Sub DBttn21_Click()
    Me.CsnDate.Caption = Format(DateSerial(Me.YrBox2, Me.MnthBox1.ListIndex + 1, Me.DBttn21.Caption), "dd-mm-yyyy")
    End Sub
    Private Sub DBttn22_Click()
    Me.CsnDate.Caption = Format(DateSerial(Me.YrBox2, Me.MnthBox1.ListIndex + 1, Me.DBttn22.Caption), "dd-mm-yyyy")
    End Sub
    Private Sub DBttn23_Click()
    Me.CsnDate.Caption = Format(DateSerial(Me.YrBox2, Me.MnthBox1.ListIndex + 1, Me.DBttn23.Caption), "dd-mm-yyyy")
    End Sub
    Private Sub DBttn24_Click()
    Me.CsnDate.Caption = Format(DateSerial(Me.YrBox2, Me.MnthBox1.ListIndex + 1, Me.DBttn24.Caption), "dd-mm-yyyy")
    End Sub
    Private Sub DBttn25_Click()
    Me.CsnDate.Caption = Format(DateSerial(Me.YrBox2, Me.MnthBox1.ListIndex + 1, Me.DBttn25.Caption), "dd-mm-yyyy")
    End Sub
    Private Sub DBttn26_Click()
    Me.CsnDate.Caption = Format(DateSerial(Me.YrBox2, Me.MnthBox1.ListIndex + 1, Me.DBttn26.Caption), "dd-mm-yyyy")
    End Sub
    Private Sub DBttn27_Click()
    Me.CsnDate.Caption = Format(DateSerial(Me.YrBox2, Me.MnthBox1.ListIndex + 1, Me.DBttn27.Caption), "dd-mm-yyyy")
    End Sub
    Private Sub DBttn28_Click()
    Me.CsnDate.Caption = Format(DateSerial(Me.YrBox2, Me.MnthBox1.ListIndex + 1, Me.DBttn28.Caption), "dd-mm-yyyy")
    End Sub
    Private Sub DBttn29_Click()
    Me.CsnDate.Caption = Format(DateSerial(Me.YrBox2, Me.MnthBox1.ListIndex + 1, Me.DBttn29.Caption), "dd-mm-yyyy")
    End Sub
    Private Sub DBttn30_Click()
    Me.CsnDate.Caption = Format(DateSerial(Me.YrBox2, Me.MnthBox1.ListIndex + 1, Me.DBttn30.Caption), "dd-mm-yyyy")
    End Sub
    Private Sub DBttn31_Click()
    Me.CsnDate.Caption = Format(DateSerial(Me.YrBox2, Me.MnthBox1.ListIndex + 1, Me.DBttn31.Caption), "dd-mm-yyyy")
    End Sub
    Private Sub DBttn32_Click()
    Me.CsnDate.Caption = Format(DateSerial(Me.YrBox2, Me.MnthBox1.ListIndex + 1, Me.DBttn32.Caption), "dd-mm-yyyy")
    End Sub
    Private Sub DBttn33_Click()
    Me.CsnDate.Caption = Format(DateSerial(Me.YrBox2, Me.MnthBox1.ListIndex + 1, Me.DBttn33.Caption), "dd-mm-yyyy")
    End Sub
    Private Sub DBttn34_Click()
    Me.CsnDate.Caption = Format(DateSerial(Me.YrBox2, Me.MnthBox1.ListIndex + 1, Me.DBttn34.Caption), "dd-mm-yyyy")
    End Sub
    Private Sub DBttn35_Click()
    Me.CsnDate.Caption = Format(DateSerial(Me.YrBox2, Me.MnthBox1.ListIndex + 1, Me.DBttn35.Caption), "dd-mm-yyyy")
    End Sub
    Private Sub MnthBox1_Change()
    If Me.MnthBox1 <> "" And Me.YrBox2 <> "" Then
    Find_my_Date
    End If
    End Sub
    Private Sub UserForm_Initialize()
    Me.TDate.Caption = Format(Date, "dd-mm-yyyy")
    With Me.MnthBox1
     For MnthList = 1 To 12
     .AddItem Format(DateSerial(2023, MnthList, 1), "MMMM")
     Next MnthList
     .Value = Format(Date, "MMMM")
      With Me.YrBox2
      For YrList = Year(Date) - 4 To Year(Date) + 3
      .AddItem YrList
      Next YrList
      .Value = Format(Date, "YYYY")
      End With
     End With
     Find_my_Date
    End Sub
    Private Sub Find_my_Date()
    Initial_D = DateSerial(Me.YrBox2, Me.MnthBox1.ListIndex + 1, 1)
    Final_D = DateSerial(Me.YrBox2, Me.MnthBox1.ListIndex + 2, 1) - 1
    For ClearDBttn = 1 To 35
    Me("DBttn" & ClearDBttn).Caption = ""
    Next ClearDBttn
    Me("DBttn" & Weekday(Initial_D)).Caption = 1
    For DayDBttn = 1 To 31
    If Me("DBttn" & DayDBttn).Caption <> "" Then
     If Me("DBttn" & DayDBttn).Caption = Format(Final_D, "dd") Then Exit Sub
     Me("DBttn" & DayDBttn + 1).Caption = Me("DBttn" & DayDBttn).Caption + 1
     End If
      For Dis_able = 1 To 35
        If Me("DBttn" & Dis_able).Caption = "" Then
        Me("DBttn" & Dis_able).Enabled = False
        Else
        Me("DBttn" & Dis_able).Enabled = True
       End If
      Next Dis_able
     Next DayDBttn
    End Sub

    You can see the result below.

  8. Reply Avatar photo
    Musiha Mahfuza Mukta Aug 28, 2023 at 4:21 PM

    Thank you Dave Gilblom, for your comment. Yes, Excel can do this. You should use some VBA codes for this. Below, I am attaching these codes.
    –> In the Module 1 write the following code. Which will return the Sum of selected cells.

    Sub CalculateSum()
        Dim selectedRange As Range
        Dim selectedArea As Range
        Dim cell As Range
        Dim sumValue As Double
        On Error Resume Next
        Set selectedRange = Application.Selection
        On Error GoTo 0
        If Not selectedRange Is Nothing Then
            sumValue = 0
            For Each selectedArea In selectedRange.Areas
                For Each cell In selectedArea
                    If IsNumeric(cell.Value) Then
                        sumValue = sumValue + cell.Value
                    End If
                Next cell
            Next selectedArea
           MsgBox "Sum: " & sumValue
        Else
           MsgBox "No valid range selected."
        End If
    End Sub

    –> In the Module 2 write the following code. Which will return the Average of selected cells.

    Sub CalculateSelectedAverage()
        Dim selectedRange As Range
        Dim avgValue As Double
        ' Check if any cells are selected
        If Selection.Cells.count > 0 Then
            ' Set the selected range
            Set selectedRange = Selection
            ' Calculate the average of the selected range
            avgValue = WorksheetFunction.Average(selectedRange)
            ' Display the result in a message box
            MsgBox "The average value of selected cells is: " & avgValue
        Else
            MsgBox "No cells are currently selected."
        End If
    End Sub

    –> In the Module 3 write the following code. Which will return the Median of selected cells.

    Sub CalculateSelectedMedian()
        Dim selectedRange As Range
        Dim cell As Range
        Dim valuesArray() As Double
        Dim medianValue As Double
        Dim i As Long, j As Long
        Dim temp As Double
        ' Check if any cells are selected
        If Selection.Cells.count > 0 Then
            ' Set the selected range
            Set selectedRange = Selection
            ' Copy selected cell values to an array
            ReDim valuesArray(1 To selectedRange.Cells.count)
            i = 1
            For Each cell In selectedRange
                valuesArray(i) = cell.Value
                i = i + 1
            Next cell
            ' Sort the values array using bubble sort (simple and not efficient)
            For i = LBound(valuesArray) To UBound(valuesArray) - 1
                For j = i + 1 To UBound(valuesArray)
                    If valuesArray(j) < valuesArray(i) Then
                        temp = valuesArray(i)
                        valuesArray(i) = valuesArray(j)
                        valuesArray(j) = temp
                    End If
                Next j
            Next i
            ' Calculate the median based on sorted values array
            If UBound(valuesArray) Mod 2 = 0 Then
                medianValue = (valuesArray(UBound(valuesArray) \ 2) + valuesArray(UBound(valuesArray) \ 2 + 1)) / 2
            Else
                medianValue = valuesArray(UBound(valuesArray) \ 2 + 1)
            End If
            ' Display the result in a message box
            MsgBox "The median value of selected cells is: " & medianValue
        Else
            MsgBox "No cells are currently selected."
        End If
    End Sub

    –> In the Module 4 write the following code. Which will return the standard deviation of selected cells.

    Sub CalculateSelectedStdDeviation()
        Dim selectedRange As Range
        Dim cell As Range
        Dim valuesArray() As Double
        Dim sum As Double
        Dim mean As Double
        Dim varianceSum As Double
        Dim stdDeviation As Double
        Dim count As Long
        ' Check if any cells are selected
        If Selection.Cells.count > 0 Then
            ' Set the selected range
            Set selectedRange = Selection
            ' Copy selected cell values to an array
            ReDim valuesArray(1 To selectedRange.Cells.count)
            count = 0
            For Each cell In selectedRange
                valuesArray(count + 1) = cell.Value
                sum = sum + cell.Value
                count = count + 1
            Next cell
            ' Calculate the mean
            mean = sum / count
            ' Calculate the sum of squared differences for variance
            For i = 1 To count
                varianceSum = varianceSum + (valuesArray(i) - mean) ^ 2
            Next i
            ' Calculate the variance and standard deviation
            If count > 1 Then
                variance = varianceSum / (count - 1)
                stdDeviation = Sqr(variance)
            Else
                variance = 0
                stdDeviation = 0
            End If
            ' Display the result in a message box
            MsgBox "The standard deviation of selected cells is: " & stdDeviation
        Else
            MsgBox "No cells are currently selected."
        End If
    End Sub

    –> Finally, in the ThisWorkbook >> write the following code.

    Private Sub Workbook_Open()
    Dim cBar As CommandBar
     Set cBar = Application.CommandBars("Cell")
     With cBar.Controls.Add(Type:=msoControlButton, Before:=1)
     .OnAction = "'" & ThisWorkbook.Name & "'!CalculateSum"
     .Caption = "Sum"
      .FaceId = 213
     .Tag = "My_Cell_Control_Tag"
     End With
      With cBar.Controls.Add(Type:=msoControlButton, Before:=1)
     .OnAction = "'" & ThisWorkbook.Name & "'!CalculateSelectedAverage"
     .Caption = "Average"
      .FaceId = 17
     .Tag = "My_Cell_Control_Tag"
     End With
      With cBar.Controls.Add(Type:=msoControlButton, Before:=1)
     .OnAction = "'" & ThisWorkbook.Name & "'!CalculateSelectedMedian"
     .Caption = "Median"
      .FaceId = 50
     .Tag = "My_Cell_Control_Tag"
     End With
     With cBar.Controls.Add(Type:=msoControlButton, Before:=1)
     .OnAction = "'" & ThisWorkbook.Name & "'!CalculateSelectedStdDeviation"
     .Caption = "StdDeviation"
      .FaceId = 2130
     .Tag = "My_Cell_Control_Tag"
     End With
    End Sub

    –> Now, save the code >> press on Run button >> close the Excel file >> re open the file >> select some cells >> right click >> from the Context Menu Bar.

    –> Then, choose the desire operation >> get the answer in MsgBox.

  9. Reply Avatar photo
    Musiha Mahfuza Mukta Aug 8, 2023 at 1:23 PM

    Thanks, KEVIN, for your suggestion. I have updated the article according to your concern. You may check it now.
    Regards
    Musiha | Team Exceldemy

  10. Reply Avatar photo
    Musiha Mahfuza Mukta Jul 23, 2023 at 5:50 PM

    Thanks for your comment, Mojtaba. You can use VBA code which should be written in the original sheet. For example, I have a sheet named “Dataset“. I have divided this sheet into 3 sheets based on row. The names of these three sheets are Sheet5, Sheet6, and Sheet7. Now, write click on “Dataset” >> from the Context Menu Bar >> select View Code.

    Write the following code in VB Editor.

    Private Sub Worksheet_Change(ByVal Target As Range)
    x = Target.Value   
    Set MyRange = Sheets("Dataset").UsedRange.Find(x)
    Y = MyRange.Row
    Z = MyRange.Column
    If Y > 11 Then
    Target.Copy Destination:=Sheets("Sheet7").Range("A1").Cells(Y - 8, Z)
    ElseIf Y > 7 Then
    Target.Copy Destination:=Sheets("Sheet6").Range("A1").Cells(Y - 4, Z)
    ElseIf Y > 3 Then
    Target.Copy Destination:=Sheets("Sheet5").Range("A1").Cells(Y, Z)
    End If
    End Sub

    Here, you must change the sheet names according to your workbook. Then you have to modify the conditions. Here, in my dataset there was 15 used rows. In the separated sheets there was 4 rows for each of them (except column headers). So, I set the conditions as row number > 11/7/3. So, when you change any cell value that value will be updated in the corresponding sheet. Like, if I change the cell value of C8 cell, then the change will be done in C4 cell of Sheet6 (as row number was 8).
    So, set all the conditions properly for all sheets, then with any change of the original sheet, you will get the updated values in other sheet too.
    Still, if you don’t get my point, then please comment or email us with the workbook. We will try to solve your problem.
    Regards
    Musiha/Exceldemy

  11. Reply Avatar photo
    Musiha Mahfuza Mukta Jul 11, 2023 at 11:17 AM

    Thank you, Salome for your comment. I’m very glad that you like the examples.
    Now, come to your question. As my understanding, you need to create a custom list for sorting. In that list MARY should be kept at first. I have explained this in 1st method. You can check this. If you want anything else, please let us know in detail.
    Thank you.

  12. Reply Avatar photo
    Musiha Mahfuza Mukta Jul 2, 2023 at 8:23 PM

    Thanks, Eliana Elia, for your comment. Step1: Select the cell where you want to keep the drop down list >> from Data tab >> Data Tools group >> Data Validation >> Data Validation >> in the dialog box, go to Settings >> List >> Select range (the listed items including “Calendar” word) in the Source box >> press OK. As a result, you will get a drop-down list in that selected cell.

    Step2: In the UserForm1, drag a new Command Button >> double click on Command Button >> write the following Code.

    Private Sub CommandButton1_Click()
    Cells(10, 2) = UserForm1.CsnDate.Caption
    End Sub

    Use your preferred cell reference.

    Step3: In B2 cell >> use this formula >> “=B3

    Step4: In VB Editor, from Insert tab >> Module >> write the following code in Module1.

    Sub running()
    If Range("A1").Cells(2, 2).Value = "Calendar" Then
    UserForm1.Show
    End If
    End Sub


    Step5: Go to Worksheet >> right click on sheet name >> from Context Menu Bar View Code >> write the following code.

    Private Sub Worksheet_Change(ByVal Target As Range)
    Set Target = Range("B2")
    If Target.Value = "Calendar" Then
    Call running
    End If
    End Sub


    Step6: Now save the code >> go back to sheet >> check the output. When you choose “Calendar” from the dropdown list >> you will get the date picker visible >> choose your date >> press Command Button1 >> get the chosen date in B10 cell. If you get any error notice, then just press “End” to that notice.

    When you selected the “Calendar” word, then with every change doing in your worksheet, you will get the date picker. So, if you need to write any other things, then first change the “Calendar” word from the drop down list.

  13. Reply Avatar photo
    Musiha Mahfuza Mukta Jun 25, 2023 at 5:20 PM

    Thank you, Pankaj for your comment. Yes, there is no built-in process in Excel, but you can manually change the alignment of data labels. I have updated the article according to your comment. You can check the process.

  14. Reply Avatar photo
    Musiha Mahfuza Mukta Jun 21, 2023 at 6:02 PM

    Here you must use IFERROR function to get blank cell for null values. Again, you need to apply formula like if there is no data then the date will be blank also.

    You must use Date format as Horizontal axis. Then you will get the chart auto updated up to valued cells. Double-click on Horizontal axis >> from Format Axis window (right side of Excel sheet) >> Axis Options >> Axis Type >> check Date axis.

  15. Reply Avatar photo
    Musiha Mahfuza Mukta Jun 21, 2023 at 3:22 PM

    Hello Daniel. You can do this by using the Find & Replace feature of Excel. From the Home tab >> under Editing group >> go to Find & Select option >> choose Replace. Then you will see the Find and Replace dialog box. Write 0 in Find what box >> write =NA() in Replace with box >> press in Find Next button >> if the cell has 0 value then press Replace button >> otherwise press Find Next.
    In this way change all the 0 values into =NA(). Don’t press Replace All as there may have numeric 0 with the numbers.
    Now insert your chart. You will get both the axis have no zero values. Below, I have attached an image where I used two axis and remove zero value from both axis.

    If you still face any problem then please provide us your worksheet in Exceldemy Forum.

  16. Reply Avatar photo
    Musiha Mahfuza Mukta Jun 13, 2023 at 1:22 PM

    Thank you, Bibhuti Sutar, for your comment. Here, you can hide the cells which you want to deselect. So, only the wanted values will be visible. In this case, you can use the following VBA code. For example, I used the given dataset. Here, I want to deselect/remove the cities named New York, Dallas, and California.

    Sub show_defined_values()
      For i = 1 To 15
        If Range("B4:D15").Cells(i, 2).Value = "New York" Then
            Range("B4:D15").Cells(i, 2).EntireRow.Hidden = True
        ElseIf Range("B4:D15").Cells(i, 2).Value = "Dallas" Then
           Range("B4:D15").Cells(i, 2).EntireRow.Hidden = True
          ElseIf Range("B4:D15").Cells(i, 2).Value = "California" Then
           Range("B4:D15").Cells(i, 2).EntireRow.Hidden = True
        End If
    Next i
    End Sub


    You must edit this according to the range. If it doesn’t work, then please inform us with more details in the reply or you can send us your workbook in Exceldemy forum.
    Regards
    Musiha Mahfuza|Exceldemy

  17. Reply Avatar photo
    Musiha Mahfuza Mukta May 3, 2023 at 11:28 AM

    Thank you, EAC for your comment. The possible solution is given below.
    • Select all the cells by clicking the triangle where row and column headers coincide.
    • Next, open the Format Cells by pressing Ctrl+1 >> Select the Protection option >> Uncheck the Locked option to unlock cells >> Click on OK.

    • Select the data range which you want to lock.
    • Again, press Ctrl+1 >> The Format Cells dialog box will pop up >> Select Protection >> Next check on the Locked option >> Click on OK.
    • Go to the Review tab in the ribbon >> Select Protect Sheet from the Protect group.
    2-How-to-block-cells-from-entering-data
    • A Protect Sheet dialog box will appear >> Set any password in the password box >> Check on the Protect worksheet and contents of locked cells>> Check both Select locked cells, Select unlocked cells.
    • A Confirm Password dialog box will appear >> Rewrite your given password >> Click on OK.

    Now, try to edit the cells. Then you will get a warning from Microsoft Excel that you can’t change anything. To edit or enter any value, you have unprotected the Excel sheet with that password first.

    Furthermore, you can see this article for more details How to Protect Excel Cells from Being Edited.

  18. Reply Avatar photo
    Musiha Mahfuza Mukta Mar 27, 2023 at 1:53 PM

    Thank you, CARLOS for your comment. You have to use the correct array (Sales Rep
    or B5:B14) in INDEX function and Rank Column (D5:D14) in MATCH function. Also, while using the Fill Handle icon, you have to freeze both arrays. The most important part, you must write Rank 1,2,3.. manually in General format in F column.
    There may have extra space or Apostrophe (‘) in the F column where you insert Rank numbers manually. You should remove all extra spaces.
    You can see our article related MATCH function error. The link is: https://www.exceldemy.com/excel-match-function-not-working/#Case_1_NA_Error
    For getting basic idea of INDEX-MATCH function you can see the examples from this article https://www.exceldemy.com/excel-index-match-example/
    Still, you are facing the problem then please comment with your used formula and sample dataset.
    Regards
    Musiha Mahfuza Mukta| Team Exceldemy.

  19. Reply Avatar photo
    Musiha Mahfuza Mukta Mar 27, 2023 at 12:44 PM

    Thanks, KYLE, for your query. If there are identical values, then it will give same Rank. Also, the RANK function will skip one Rank. For your better understanding, I’m changing the sales value of Janifer to $9158. So, Zuschuss and Janifer get the same rank (2nd). Thus, the 3rd rank will be missed. Here, you must re-write the Rank of F7 cell to “2” and change the array for INDEX-MATCH function using in G7 an H7 cell. Basically, you need to set the array without Zuschuss information. Below, I have attached the whole scenario. getting duplicate rank
    There is another way, if you want to get unique Rank for all. Like Zuschuss comes first than Janifer so Zuschuss will get 2nd rank and Janifer will get 3rd rank. In this case, you just need to change the formula in D column given below: =RANK(C5,$C$5:$C$14,0)+COUNTIF($C$5:C5,C5)-1
    You don’t need to change the array of INDEX-MATCH function. getting unique rank
    Regards
    Musiha Mahfuza Mukta| Team Exceldemy

  20. Reply Avatar photo
    Musiha Mahfuza Mukta Mar 27, 2023 at 11:41 AM

    Hi ATIF, Thanks for your comment. Here, I made a dataset keeping the Text value in A column, Date value in B column and Counter will be in C column. If you provide your Excel file, then it will be more useful. As per my understanding, I am providing the following VBA code.
    From Developer tab >> go to Visual Basic >> Insert a Module >> copy the code in that >> from Macros >> Run the code.
    code for days counter

    Sub Days_Counter()
    Count = 0
    For i = 1 To 11
    If Range("B1").Cells(i) <> Range("B1").Cells(i + 1) _
    And Range("A1").Cells(i) = "Normal" Then
        Count = Count + 1
        Range("C1").Cells(i) = Count
    Else: Range("C1").Cells(i) = 0
    End If
    Next i
    End Sub

    You can see the outcome below.
    output
    Where the counter counts single value for same date. I have highlighted the same date. Also, for “Abnormal” text the counter didn’t count.
    In the code, you must mention total Row number of your dataset in For Next loop.
    If you want only the text part, you can remove this portion

    Range("B1").Cells(i) <> Range("B1").Cells(i + 1)

    from code.
    Hopefully this will work. If you are still facing problem, then please comment with more details or the sample dataset.
    Regards
    Musiha Mahfuza Mukta| Team Exceldemy

  21. Reply Avatar photo
    Musiha Mahfuza Mukta Mar 9, 2023 at 12:11 PM

    Thank you, MICHAEL KAIR for your comment. As per my understanding, there is no duplicate code. Actually, I have written the code part by part with detail description. Also, in the end, I have attached the complete code for making the Date Picker. That’s why, it seems to you the code is used twice but actually, the last one in Step 7 (last code) is the complete one. You should use only that one in your module. And the other codes are for explanation purpose.
    I hope this will solve your problem. Please let us know if you face any further problems.

    Regards,
    Musiha Mahfuza Mukta,
    ExcelDemy

  22. Reply Avatar photo
    Musiha Mahfuza Mukta Feb 27, 2023 at 10:58 AM

    Thank you ZOYSA for your comment. Below, I have attached two formulas for your problem.
    I have written the data from the A2 cell and C2 cell. According to your question, I have considered the dataset till A10 and C10.
    Firstly, write the below formula in the E2 cell or the cell from where the NAME will be started.
    =IF(A2=”TOM”,C2,””)

    Then copy this formula up to E10 or your dataset’s end cell.
    Then use another formula in the F4 cell.
    =SUM(E2:E10)

  23. Reply Avatar photo
    Musiha Mahfuza Mukta Feb 19, 2023 at 12:33 PM

    Thank you PHUC YU for your comment. Actually, I have tried these methods too and the methods are working perfectly. Here, you can also zoom out the Excel file directly by clicking the Minus(-) sign situated right most corner of the file.

    In case of, you are using an older version than 2013 of Excel then these methods may not work. Or, if you have any bugs or issues in your laptop then these would not work. I thing you were facing a different problem which is not related to this articles.

  24. Reply Avatar photo
    Musiha Mahfuza Mukta Feb 19, 2023 at 11:13 AM

    Thank you ROB for your comment. Here, ROW function is working as the row index number of the INDEX function. Actually, it should be ROW(B2:D9)=> {2;3;4;5;6;7;8;9} which denotes respectively the 2nd, 3rd, 4th…. up to 9th row number of this ($B$4:$D$12) array of INDEX function. Here, we ignore the 1st row of ($B$4:$D$12) this array as 1st row contains the Club Name’s not any player Name’s.
    On the other hand, you have to use B4:D4 row in the MATCH function which will search for the Club Name’s and act as the column index number in the INDEX function.
    Say, when you choose Borussia Dortmund or C4 cell from the list of C14 cell then the MATCH function will return 2 and thus the column index number of INDEX function will be 2 so INDEX function will give all the rows (except 1st one) of 2nd column of the given array which means all the player name’s of the Borussia Dortmund club.
    So, you can use the formula like the below one. =IFERROR(INDEX($B$4:$D$12,ROW(B2:D9),MATCH($C$14,$B$4:$D$4,0)),””)
    Use of INDEX, MATCH, ROW functions

  25. Hello MP ROY,
    Thank you for your comment. I have tried these codes and they are working perfectly, except the code in Example3. For that particular code, you can use a new workbook. While I was using a new workbook the code has been perfectly worked there. But you have to be careful about the name of worksheet. You have to use exact worksheet number and name in the code.
    Regards,
    Musiha
    Team ExcelDemy

  26. Hello, J KUMAR.
    Thank you for your comment. I have tried the code too and the code is absolutely right and perfectly working. But you are facing problem because most probably your device is running out of virtual memory. So, when you are trying this code in Excel, at that time you should close all other applications. Also, you should use an individual module for this code.

  27. Hello, CHRIS.
    Thank you for your comment. Actually, with the help of method 4, you are converting numbers into text. But when you re-entered any new value then the cell will hold that value excluding the apostrophe. Basically, the past value along with the apostrophe completely had gone away. So, if you want to keep the apostrophe then you should select that cell (containing new value) and run the Macros again. Then, you will see the apostrophe again with the new value.

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo