In this study, the row is copied to the sheet (e.g On_hire sheet) according to the value selected from the drop-down list in column H, and the row is deleted.
The following steps have been taken for data parsing into multiple sheets :
- "Options" name is defined for Range("J1:J3")
- Drop-down lists is created in Column H using Data Validation - List Method. The name we defined is entered to the "Source" section.
- The macro codes are added to Worksheet_Change Function in Vbe window : "Private Sub Worksheet_Change(ByVal Target As Range) Dim i As Long If Target.Column = 8 Then For i = 5 To Me.Cells(Rows.Count, 1).End(xlUp).Row If Cells(i, "H").Value = "On hire" Then Rows(i).Copy Sheets("On_hire").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues Rows(i).Delete i = i - 1 ElseIf Cells(i, "H").Value = "Off hire" Then Rows(i).Copy Sheets("Off_hire").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues Rows(i).Delete i = i - 1 ElseIf Cells(i, "H").Value = "On sales" Then Rows(i).Copy Sheets("On_sales").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues Rows(i).Delete i = i - 1 End If Next i End If End Sub"
If item of listbox is clicked, the employee's informations
are displayed on rows in the sheet (e.g employee’s image,staff no, job title,
date of employment).With spinbutton can be navigated between items of
the listbox ,any value can be searched with textbox.
If Excel workbook contains many sheets , a table of contents
can be created to navigate easier between the sheets.
This process can be done with macro .
This macro will create a new sheet at the start of the
workbook named "Workbook_Index" when workbook opened. If this sheet
already exists it will remove it and will rebuild. The macro will then list the
names of all the sheets in the workbook and insert a hyperlink for each
one.Also,can be returned to table of contents when the "Esc" key is
pressed while on any sheet.
The VBA code is displayed below. Copy and paste codes into
the module of a workbook where you need to create a table of contents.
-Press Alt+F11 keys on any sheet to open Visual Basic Editor
-Right-click on your workbook name in the
"Project-VBAProject" pane (at the top left corner of the editor
window) and select Insert -> Module from the context menu.
-Copy the VBA code below and paste it to the right pane of
the VBA editor ("Module1" window).
-Confirm the changes , close the workbook and reopen it.
Macro code to add into module :
Dim Page As Worksheet
Dim k, m As Integer
k = 1
m = 1
For Each Page In Worksheets
Sheets("Workbook_Index").Cells(k, 1).Value = m & "-"
Hexadecimal(Hex) color codes frequently are used in web designing
(HTML-CSS etc.) .
In Excel, the RGB and HLS color codes are used. Therefore, it is necessary to
convert Hex values to RGB or HLS.
For example,consider we have a hexadecimal code for a cell
background color in Excel ; #FF8800 . This code corresponds to orange color.When we convert this code to rgb with userform that we created, the
result is RGB(255,136,0). So, we can use the color code as Rgb in Excel and
For example : "Sheets("Page-1").Range("f11").Interior.Color = RGB(255, 136, 0) TextBox1.BackColor = RGB(255, 136, 0)"