Entering Numbers That Start With A Zero

Entering Numbers That Start With A Zero In Excel

        Excel won't display the leading zeros by default in cell.
When user entered the leading zeros, Excel applies the General numeric format and removes out the leading zero. Excel won't display  the leading 0 digit .

As a solution, the following ways can be applied :
- An apostrophe character (') is put precede the value . In this way ,user notifies to Excel the value is text because it's left-aligned . The apostrophe can be seen  in the Formula bar.

excel leading zero

- The cell is selected.
Right-click on the selected cell or range and choose Format Cells from the context menu.
The Number tab is clicked.
Custom is selected from the Category list.
In the Type field, the number 0 required to accommodate the largest value is entered. For example, if the largest value contains four digits, four 0 is entered.



        But, this format can't be used with decimal values. For example,the value .8 entered , this format rounds the value to 1. To accommodate decimal values, placeholders for the appropriate number of digits are entered . For instance, if it is wanted to display four digits to the left and two digits to the right of the decimal,  the custom format 0000.00 is entered.

Chemical Elements In Excel

Excel Vba Chemical Elements Table

        We created a table of chemical elements in Excel workbook. We have added a simple Excel userform to make it easier to review the properties of chemical elements.

 Our userform has a combobox, a button and text boxes .


Our codes :
- To fill the combobox control (in Userform1_Initialize() procedure):
lastrow = Sheets("Chemical_Elements").Cells(Rows.Count, "A").End(xlUp).Row 
For i = 2 To lastrow 
ComboBox1.AddItem Cells(i, 1) 
Next i

To fill textboxes according to the value in combobox (in ComboBox1_Change() procedure):
i = UserForm1.ComboBox1.ListIndex
symbol = Range(“A2:A” & n).Cells(i + 1, 3)
atomicno = Range(“A2:A” & n).Cells(i + 1, 4)
atomicmass = Range(“A2:A” & n).Cells(i + 1, 2)

- To highlight cell when selected the combobox item (in ComboBox1_Change() procedure):
lastrow = Sheets(“Chemical_Elements”).Cells(Rows.Count, “A”).End(xlUp).Row
Sheets(“Chemical_Elements”).Activate
Sheets(“Chemical_Elements”).Range(“A2:A” & lastrow).Find(What:=ComboBox1.Value,
LookIn:=xlValues, LookAt:=xlWhole).Activate

Insert Picture Dynamically In Cell Based On Cell Value & Delete Picture

Excel Vba Insert Picture To Cell And Delete Picture In Cell

How To Insert Image Or Picture Dynamically In Cell Based On Cell Value In Excel?

           In the template that we created earlier, only we added images from the folder according to the cell value .
We were entering a value in column A, and if there were any pictures that match that value, that picture was adding to column E (For example ; value:A01 ,the added image to Column E :A01.jpg).
When the value in column A was deleted or changed, the image in column E was staying fixed in his cell.
           For efficient use of the template, when the value in column A is deleted or changed, the image associated with this value must be deleted.
To this end, we have made the changes in Vba codes to delete picture from cell .

Our codes:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim pic As Picture
If Intersect(Target, [A:A]) Is Nothing Then Exit Sub

On Error GoTo son
For Each pic In ActiveSheet.Pictures
    If Not
Application.Intersect(pic.TopLeftCell, Range(Target.Offset(0, 4).Address)) Is
Nothing Then

        pic.Delete
    End If
Next pic
ActiveSheet.Pictures.Insert(ThisWorkbook.Path & "\" & Target.Value & ".jpg").Select
Selection.Top = Target.Offset(0, 2).Top
Selection.Left = Target.Offset(0, 4).Left
Selection.ShapeRange.LockAspectRatio = msoFalse
Selection.ShapeRange.Height = Target.Offset(0, 2).Height
Selection.ShapeRange.Width = Target.Offset(0, 4).Width
Target.Offset(1, 0).Select
son:
 End Sub

The loop that we created to delete picture in cell:
For Each pic In ActiveSheet.Pictures
    If Not Application.Intersect(pic.TopLeftCell, Range(Target.Offset(0, 4).Address)) Is Nothing Then
        pic.Delete
    End If
Next pic

The user may want to add the picture of the value in column A to column C, not to column E. In this case it is necessary to make changes to the codes:
"For Each pic In ActiveSheet.Pictures
    If Not Application.Intersect(pic.TopLeftCell, Range(Target.Offset(0, 2).Address)) Is Nothing Then
        pic.Delete
    End If
Next pic
ActiveSheet.Pictures.Insert(ThisWorkbook.Path & "\" & Target.Value & ".jpg").Select
Selection.Top = Target.Offset(0, 2).Top
Selection.Left = Target.Offset(0, 2).Left
Selection.ShapeRange.LockAspectRatio = msoFalse
Selection.ShapeRange.Height = Target.Offset(0, 2).Height
Selection.ShapeRange.Width = Target.Offset(0, 2).Width
Target.Offset(1, 0).Select
son: "