Bishawajit Chakraborty

Thank you so much Sharath for your response. If you have any questions, comments, or recommendations, kindly leave them in the comment section below.

Regards,

Bishawajit Chakraborty.

Thank you, DAWOOD, for your wonderful question.

In Excel, you can use a barcode font to create barcodes that include hyphens (“-“). Here are the steps to do it:

1. Download and Install a Barcode Font:

• First, you’ll need to download and install a barcode font that supports hyphens. You can find various barcode fonts online, and some popular ones include Code 128, Code 39, or EAN-13. Download the font and install it on your computer following the font installation instructions for your operating system.

2. Enter Your Barcode Data:

• In an Excel cell, enter your barcode data, including the hyphens.
• Select all the codes in the cell.
• From the Font group in the Home tab, choose Code 128 font format.
• Press Enter.

• Now, you will see the following result.

I hope this may solve your issue. Also, please follow this article to get your answer with detailed explanations.

That’s it! You’ve created a barcode in Excel with hyphens included in the data. Remember that the actual appearance and functionality of the barcode depend on the specific barcode font you’re using, so make sure to choose a font that supports the format you need.

Bishawajit, on behalf of ExcelDemy

Thank you, STEVEN BRITTON, for your wonderful question.

Here is the explanation to your question.

It is true that the “Microsoft Date and Time Picker Control 6.0 (SP6)” is no longer available in Windows 11 and more recent versions of Microsoft Office. This control is no longer supported by Microsoft and is no longer included in the default ActiveX control set.

The most recent editions of Excel, including Windows 11, allow you to enter a date picker using a different method. You can now choose dates in Excel by using the built-in Microsoft “Calendar Control” date picker.

This article will help you how to add date and time picker control. Check this below link.

How to Use Excel UserForm as Date Picker

I hope this may solve your issue. 

Bishawajit, on behalf of ExcelDemy

Thank you, KC, for your wonderful question.

Here is the explanation to your question.

It is true that the “Microsoft Date and Time Picker Control 6.0 (SP6)” is no longer available in Windows 11 and more recent versions of Microsoft Office. This control is no longer supported by Microsoft and is no longer included in the default ActiveX control set.

The most recent editions of Excel, including Windows 11, allow you to enter a date picker using a different method. You can now choose dates in Excel by using the built-in Microsoft “Calendar Control” date picker.

This article will help you how to add date and time picker control. Check this below link.

How to Use Excel UserForm as Date Picker

I hope this may solve your issue. 

Bishawajit, on behalf of ExcelDemy

Thank you, FAJAR, for your wonderful question.

Here is the explanation to your question.

It is still possible for someone to obtain the cell value from another workbook without opening it using Excel VBA even if the source file is password- or other security-protected.

However, depending on the situation, accessing a protected file in this manner might be regarded as unethical or unlawful. It’s crucial to respect the security precautions taken by the file’s owner and to only access the data through legitimate, approved methods. Additionally, it is important to keep in mind that Excel offers a variety of protection options, and the efficacy of the protection depends on the particular technique used. While some security measures can be easily bypassed with a basic understanding of VBA, others are more robust and call for sophisticated techniques.

Because of this, it’s crucial to carefully examine the degree of protection needed for your unique use case and implement the proper security measures in accordance.

I hope this may solve your issue. 

Bishawajit, on behalf of ExcelDemy

Thank you, Wlad, for your wonderful question.

Here is the explanation for your question.

Array Looping: The “For” loop iterates through the values in the “arr” array. The “LBound(arr, 1)” method returns the lower bound of the array’s first dimension, while the “UBound(arr, 1)” function delivers the upper bound of the array’s first dimension. The loop counter “i” takes values from the lower bound to the higher bound of the “arr” array’s first dimension.

Empty Loop: Because there are no statements or actions between the “For” and “Next” keywords, the loop looks to be an empty loop that does nothing significant.

I hope this may solve your issue. 

Bishawajit, on behalf of ExcelDemy

Thank you, SIDDHARTH, for your wonderful question.

Here is the solution to your question. Please take a look at the below steps.

• Create a table with columns for Roll Number, Name, and Marks in Excel.
• Enter the Roll Number, Name, and Marks for each student in the table.
• Create a new sheet in Excel and name it “Marksheets”.
• In cell A1 of the “Marksheets” sheet, enter the text “Roll Number”.
• In cell B1, enter the text “Name”.
• In cell C1, enter the text “Marks”.
• Select cell A2 on the “Marksheets” sheet.

• Go to the “Data” tab in the Excel ribbon and click on “Data Validation”.
• In the “Data Validation” window, select “List” as the validation criteria.
• In the “Source” field, enter the range of Roll Numbers from the table you created earlier.
• Click “OK” to close the “Data Validation” window.
• Repeat steps for cells B2 and C2, but select the range of Names and Marks respectively.
• Now, select the Roll Number, Name, and Marks here.

Here is our  VBA code. Therefore, you can apply this code to solve your problem.

Sub Generate_Marksheet()
Dim roll_number As String
Dim name As String
Dim marks As Integer
Dim i As Integer
i = 2 'Starting at row 2 of the Marksheets sheet
Do While Worksheets("Marksheets").Cells(i, 1) <> "" 'Loop until the Roll Number column is empty
roll_number = Worksheets("Marksheets").Cells(i, 1).Value
name = Worksheets("Marksheets").Cells(i, 2).Value
marks = Worksheets("Marksheets").Cells(i, 3).Value
'Creating a new sheet for each Roll Number
Worksheets.Add(After:=Worksheets(Worksheets.Count)).name = roll_number
'Adding the Name and Marks to the sheet
Worksheets(roll_number).Range("A1").Value = "Name"
Worksheets(roll_number).Range("B1").Value = "Marks"
Worksheets(roll_number).Range("A2").Value = name
Worksheets(roll_number).Range("B2").Value = marks
i = i + 1 'Moving to the next row
Loop
End Sub

Here, you will see the final result in another sheet with names and marks.

I hope this may solve your issue. 

Bishawajit, on behalf of ExcelDemy

Hi NICOLÁS,
Thanks for following our article.  Please go through the whole article and create the template. And, you can use one ticker for only one sheet to import stock prices from any other website.
Let us know if your problem is fixed.
Regards,
Bishawajit Chakraborty
Author at ExcelDemy

Thank you, MEREDITH, for your wonderful question. 

Here is our modified VBA code. Therefore, you can apply this code to solve your problem but you can also modified this code for three digit numbers by using word “and” in our code section.

Here is the image of modified code where we have added the word “and”.

Function word(SNum As String)
'Declare the Variables
Dim zDPInt As Integer
Dim zArrPlace As Variant
Dim zRStr_Paisas As String
Dim zNumStr As String
Dim zP As Integer
Dim zTemp As String
Dim zStrTemp As String
Dim zRStr As String
Dim zBp As Integer
zArrPlace = Array("", "", " Thousand ", " Lacs ", " Crores ", " Trillion ", "", "", "", "")
On Error Resume Next
If SNum = "" Then
word = ""
Exit Function
End If
zNumStr = Trim(Str(SNum))
If zNumStr = "" Then
word = ""
Exit Function
End If

zRStr = ""
zBp = 0
If (zNumStr > 999999999.99) Then
word = "Digit excced Maximum limit"
Exit Function
End If
zDPInt = InStr(zNumStr, ".")
If zDPInt > 0 Then
If (Len(zNumStr) - zDPInt) = 1 Then
zRStr_Paisas = word_GetT(Left(Mid(zNumStr, zDPInt + 1) & "0", 2))
ElseIf (Len(xNumStr) - xDPInt) > 1 Then
zRStr_Paisas = word_GetT(Left(Mid(zNumStr, zDPInt + 1), 2))
End If
zNumStr = Trim(Left(zNumStr, zDPInt - 1))
End If
zP = 1
Do While zNumStr <> ""
If (zP >= 2) Then
zTemp = Right(zNumStr, 2)
Else
If (Len(zNumStr) = 2) Then
zTemp = Right(zNumStr, 2)
ElseIf (Len(zNumStr) = 1) Then
zTemp = Right(zNumStr, 1)
Else
zTemp = Right(zNumStr, 3)
End If
End If
zStrTemp = ""
If Val(zTemp) > 99 Then
zStrTemp = word_GetH(Right(zTemp, 3), zBp)
If Right(Trim(xStrTemp), 3) <> "Lac" Then
zBp = zBp + 1
End If
ElseIf Val(zTemp) <= 99 And Val(zTemp) > 9 Then
zStrTemp = word_GetT(Right(zTemp, 2))
ElseIf Val(zTemp) < 10 Then
zStrTemp = word_GetD(Right(zTemp, 2))
End If
If zStrTemp <> "" Then
zRStr = zStrTemp & zArrPlace(zP) & zRStr
End If
If zP = 2 Then
If Len(zNumStr) = 1 Then
zNumStr = ""
Else
zNumStr = Left(zNumStr, Len(zNumStr) - 2)
End If
ElseIf zP = 3 Then
If Len(zNumStr) >= 3 Then
zNumStr = Left(zNumStr, Len(zNumStr) - 2)
Else
zNumStr = ""
End If
ElseIf zP = 4 Then
zNumStr = ""
Else
If Len(zNumStr) <= 2 Then
zNumStr = ""
Else
zNumStr = Left(zNumStr, Len(zNumStr) - 3)
End If
End If
zP = zP + 1
Loop
If zRStr = "" Then
zRStr = "No Rupees"
Else
zRStr = " Rupees " & zRStr
End If
If zRStr_Paisas <> "" Then
zRStr_Paisas = " and " & zRStr_Paisas & " Paisas"
End If
word = zRStr & zRStr_Paisas & " Only"
End Function
Function word_GetH(zStrH As String, zBp As Integer)
Dim zRStr As String
If Val(zStrH) < 1 Then
word_GetH = ""
Exit Function
'Converts a Number from 100 to 999 into Word
Else
zStrH = Right("000" & zStrH, 3)
If Mid(zStrH, 1, 1) <> "0" Then
If (zBp > 0) Then
zRStr = word_GetD(Mid(zStrH, 1, 1)) & " Lac "
Else
zRStr = " and " & word_GetD(Mid(zStrH, 1, 1)) & " Hundred "
End If
End If
If Mid(zStrH, 2, 1) <> "0" Then
zRStr = zRStr & word_GetT(Mid(zStrH, 2))
Else
zRStr = zRStr & word_GetD(Mid(zStrH, 3))
End If
End If
word_GetH = zRStr
End Function
Function word_GetT(zTStr As String)
Dim zTArr1 As Variant
Dim zTArr2 As Variant
Dim zRStr As String
'Converts a Number from 10 to 19 into Word

zTArr1 = Array("Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", "Eighteen", "Nineteen")
'Converts a Number from 20 to 99 into Word

zTArr2 = Array("", "Twenty", "Thirty", "Forty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninety")
Result = ""
If Val(Left(zTStr, 1)) = 1 Then
zRStr = zTArr1(Val(Mid(zTStr, 2, 1)))
Else
If Val(Left(zTStr, 1)) > 0 Then
zRStr = zTArr2(Val(Left(zTStr, 1)) - 1)
End If
zRStr = zRStr & word_GetD(Right(zTStr, 1))
End If
word_GetT = zRStr
End Function
Function word_GetD(zDStr As String)
Dim zArr_1() As Variant
'Converts a Number from 1 to 9 into Word

zArr_1 = Array(" One", " Two", " Three", " Four", " Five", " Six", " Seven", " Eight", " Nine", "")
If Val(zDStr) > 0 Then
word_GetD = zArr_1(Val(zDStr) - 1)
Else
word_GetD = ""
End If
End Function

Here, you will see the final result.

I hope this may solve your issue. 

Bishawajit, on behalf of ExcelDemy

Thank you, Fred, for your wonderful question.

Firstly, go to the Home tab.

You can keep the decimal places of any number at any place by using the Increase Decimal and the Decrease Decimal icons here.

Bishawajit, on behalf of ExcelDemy

Thank you, JOHN SCURAS, for your comment.

You can use the below formula to determine your commission of the model.

=IF(H36>C33,SUMPRODUCT(–(H36>$C$33:$C$56),(H36-$C$33:$C$56),$D$33:$D$56)+C33*E33,C33*E33)

Here, H36 is the sales amount, C33 is your first Deal Size Range (High), C33:C56 is all values for Deal Size Range (High) column, D33:D56 is all values from the % incremental commission column, and E33 is the first value of the incremental commission (5%).

Best Regards,
Bishwajit
Team ExcelDemy

Greetings AND74,
Thanks for noticing the error.
It will be OR(C5QUARTILE($C$5:$C$14,3)+1.5*$D$16) instead of OR(C5QUARTILE($C$5:$C$14,1)+1.5*$D$16).
We will make the corrections shortly.

Best Regards,
Bishwajit
Team ExcelDemy

Thank you Edijs for your wonderful question. To edit the cell range follow these steps:

Firstly, enable the Microsoft Forms 2.0.

Secondly, type this code on to sheet 2.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    If Not Application.Intersect(Range("D5:D10"), Target) Is Nothing Then
    Dim xDataObj As New MSForms.DataObject
    Dim rowNumber As Integer
    rowNumber = Split(Target.Address, "$")(2)
    xDataObj.SetText rowNumber
    xDataObj.PutInClipboard
        If IsNumeric(Target.Value) And Target.Value > 700 Then
            Call Send_Email_Condition_Cell_Value_Change
        End If
    End If
End Sub

Thirdly, type change the code from Module 1.

Sub Send_Email_Condition_Cell_Value_Change()
    Dim pApp As Object
    Dim pMail As Object
    Dim pBody As String
    Dim xDataObj As New MSForms.DataObject
    xDataObj.GetFromClipboard
    Set pApp = CreateObject("Outlook.Application")
    Set pMail = pApp.CreateItem(0)
    pBody = "Hello, " & Cells(xDataObj.GetText, 2).Value & vbNewLine & _
              "You've Payment Due." & vbNewLine & _
              "Please Pay it to avoid extra fees."
    On Error Resume Next
    With pMail
        .To = Cells(xDataObj.GetText, 3).Value
        .CC = ""
        .BCC = ""
        .Subject = "Request For Payment"
        .Body = pBody
        .Display  'We can use .Send to Send the Email
    End With
    On Error GoTo 0
    Set pMail = Nothing
    Set pApp = Nothing
End Sub

This should solve your problem. You can see the output from the following animated image.

If you have any further question, please let us know.

Regards

Bishawajit, on behalf of ExcelDemy

Thank you Prachi Davade for your wonderful question. You can change the

Set XDueDate = Application.InputBox("Select the column for Deadline/Due Date date column:", "ExcelDemy", , , , , , 8)

to

Set XDueDate = Range("D5:D7")

This will do what you desire. If you have further queries, let us know.

Regards

Bishawajit, on behalf of ExcelDemy

Thank you so much, Peter, for your comment.
You will find your solution in the below steps. Please follow the steps accordingly.

Steps:
• Firstly, go to the Data tab.
• And, select the group option.

• Now, you will see here the plus (+) sign.
• Here, we have grouped the 5th, 6th, and 7th rows.
• Then, go to the Review tab.
• And, select the Allow Edit Ranges option.
• After that, choose the ranges.
• And, type the password and click OK.

• Retype the password and click OK.

• Now remove this window.

• Firstly, go to the Review tab.
• Secondly, select the Protect Sheet option.

• Now, write your password.
• And, click OK.

• Then, retype your password and click OK.

• So, if you click on the plus (+) sign, it will show you the given result you want.

Thank you, Dhroov, for your comment. In our Excel file, this formula is working. Can you please share your excel file with us? Email Address: [email protected]. We will try to solve your problem as soon as possible.