So I have this dropdown list in excel....I have this 7 departments in the list a
ID: 664684 • Letter: S
Question
So I have this dropdown list in excel....I have this 7 departments in the list and I want each of these departments to select their name in the list then add their data. Is this possible with VBA atleast. I want it to llok up some data but I also want each name in the list to be updatable. What the best way to do this??
Gantt Project Updated 3.0x-Exce VIEW DEVELOPER ADD-INS AS DATA REVIEW VEW DEVELOPER ADD-INS S DATA REVIEW General Percent 4 yTitle 2 Total 2 Style 1 Conditional Format as| Normal Formatting Table &Center; . $ .96 , $ . % , Bad Good Neutral Alignment Styles sabled Enable Content Produce Sporting Goods Cathiers Comments Type AM Shif Start PM Shift Start Back Office Start Lunch Break Start Position Stations ScratchExplanation / Answer
Yes, it is possible - first we create the data in the MS Excel spread sheet and then press Alt + F11 to open the limited VB ( = VBA = Visual Basic for Applications) - which has some features of VB but not as strong as VB.Net - but for this task VBA is sufficient.
It is best to use a combo box:
With Sheet1Departments.ComboBox2
.AddItem "Legal Advices Department"
.AddItem "Education counselling Advices Dept"
.AddItem "Management Studies Advices Dept"
End With
Just for understanding I have shown the hard coded code snippet.
In actual coding, should replace the above values with the user input as shown in the code snippet below:
position = InputBox("Enter Position")
comments = InputBox("Enter comments")
DeptType = InputBox("Enter DeptType")
AllShiftStart = InputBox("Enter All Shift Start time ")
AllShiftEnd = InputBox("Enter All Shift End Time")
PMShiftStart = InputBox("Enter PM Shift Start Time")
PMShiftEnd = InputBox("Enter PM Shift End Time")
BackOfficeStart = InputBox("Enter BackOfficeStart Time ")
BackOfficeEnd = InputBox(" Enter BackOfficeEnd Time")
LunchBreakStart = InputBox(" Enter LunchBreakStart Time")
LunchBreakEnd = InputBox(" Enter LunchBreakEnd Time")
With Sheet1Departments.ComboBox2
.AddItem position
.AddItem comments
.AddItem DeptType
.AddItem AllShiftStart
.AddItem AllShiftEnd
.AddItem PMShiftStart
.AddItem PMShiftEnd
.AddItem BackOfficeStart
.AddItem BackOfficeEnd
.AddItem LunchBreakStart
.AddItem LunchBreakEnd
End With
Regarding the answer for your second comment of asking how to explain what is 39, it represents the cell id - the row and column once you are inside the VBA module for example,
How to do this:
Created a s ample Drop down List of around 25 departments ( more than 7 departments - including the departments given in the question and also some extra departments) as shown in the screen capture below:
And also created the line of headings at row 36 from Position, Comments, Type, All Shift Start Al Shift End etc up to Lunck Break End. Also added a new column heading called Department Chosen to ensure Logic.
code for filling data:
Dim position, comments, DeptType As String
Dim AllShiftStart, AllShiftEnd, PMShiftStart, PMShiftEnd As String
Dim BAckOfficeStart, BackOfficeEnd, LunchBreak As String
Private Sub DeptCommandButton1_Click()
'MsgBox (" will show list of departments from excel sheet")
' From the second line onwards - from row 40 - we will fill user entered values:
position = InputBox("Enter Position")
comments = InputBox("Enter comments")
DeptType = InputBox("Enter DeptType")
AllShiftStart = InputBox("Enter All Shift Start time ")
AllShiftEnd = InputBox("Enter All Shift End Time")
PMShiftStart = InputBox("Enter PM Shift Start Time")
PMShiftEnd = InputBox("Enter PM Shift End Time")
Sheet1Departments.Cells(40, 2) = position ' this worked correctly for a position value of CEO
Sheet1Departments.Cells(40, 3) = comments
Sheet1Departments.Cells(40, 4) = "Type Admin "
Sheet1Departments.Cells(40, 5) = "All shift Start 7:55 am "
Sheet1Departments.Cells(40, 6) = "All shift End 3:55 pm "
Sheet1Departments.Cells(40, 7) = "PM Shift Start 3 pm "
Sheet1Departments.Cells(40, 8) = "PM Shift End 1 am "
Sheet1Departments.Cells(40, 9) = "Back Office Start 7 am "
Sheet1Departments.Cells(40, 10) = "Back Office End 2 am "
Sheet1Departments.Cells(40, 11) = "Lunch Break Start 12 noon "
Sheet1Departments.Cells(40, 12) = "Lunch Break End 1 pm "
' Just the first line is hard coded as a sample
Sheet1Departments.Cells(39, 2) = "PositionManager"
Sheet1Departments.Cells(39, 3) = "Comments Manger is responsible for management"
Sheet1Departments.Cells(39, 4) = "Type Admin "
Sheet1Departments.Cells(39, 5) = "All shift Start 7:55 am "
Sheet1Departments.Cells(39, 6) = "All shift End 3:55 pm "
Sheet1Departments.Cells(39, 7) = "PM Shift Start 3 pm "
Sheet1Departments.Cells(39, 8) = "PM Shift End 1 am "
Sheet1Departments.Cells(39, 9) = "Back Office Start 7 am "
Sheet1Departments.Cells(39, 10) = "Back Office End 2 am "
Sheet1Departments.Cells(39, 11) = "Lunch Break Start 12 noon "
Sheet1Departments.Cells(39, 12) = "Lunch Break End 1 pm "
' Sheet1Departments.Cells(39, ) = " "
End Sub
Ofcourse Combo box will allow both selection and also addition of new department names at run time,
But for list box, we can bring in the functionality of a combo box, by adding the following code snippet in the VBA module:
Sub Fill_Control()
Dim DepartmentDialog As Object
Dim DepartmentList As Object
Dim DepartmentDropDown As Object
Set DepartmentDialog = DialogSheets("Dialog1")
Set DepartmentList = DepartmentDialog.ListBoxes("List Box4")
Set DepartmentDropDown = DepartmentDialog.DropDowns("DropDown 5")
'Remove all items from drop-down and list box
'DepartmentList.RemoveAllItems
'DepartmentDropDown.RemoveAllItems
'Insert data into List Box and Drop Down
myNewDeptArray = Array("Education", "Spectacles", "Stationaries", "Clothings", "Dresses")
For variableX = 0 To 4
DepartmentList.AddItem myNewDeptArray(variableX)
DepartmentDropDown.AddItem myNewDeptArray(variableX)
Next variableX
'Show the updated Dialog Box
DepartmentDialog.Show
End Sub
Also in the excel spread sheet, we should go to the heading cell where we have entered as Dynamic Department List and press control + F3 - that will open the Name dialog box where you should enter the following formula in the Refers to box:
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)
where Sheet1 refers to the active sheet where we had entered the data
OFFSET function has the following syntax:
OFFSET(reference, rows, cols, [height], [width])
The entire VBA code inside a module :
Dim drpDn As DropDown
Dim DepartmentChosen As String
Dim position, comments, DeptType As String
Dim AllShiftStart, AllShiftEnd, PMShiftStart, PMShiftEnd As String
Dim BackOfficeStart, BackOfficeEnd, LunchBreakStart, LunchBreakEnd As String
Private Sub DeptCommandButton1_Click()
'MsgBox (" will show list of departments from excel sheet")
' From the second line onwards - from row 40 - we will fill user entered values:
' DepartmentChosen = Sheet1Departments.Cells(5, 6).chosenvalue
'Set drpDn = Sheet1Departments.DropDowns("Drop Down 6") ' method dropdowns for object sheet1depts must be available
'MsgBox drpDn
'Set Rng = Sheet2.Range(drpDn.ListFillRange)
'MsgBox Rng
'Set drpDnValue = Rng(drpDn.Value)
'MsgBox drpDnValue
With Sheet1Departments.ComboBox2
.AddItem "Legal Advices Department"
.AddItem "Education counselling Advices Dept"
.AddItem "Management Studies Advices Dept"
End With
position = InputBox("Enter Position")
comments = InputBox("Enter comments")
DeptType = InputBox("Enter DeptType")
AllShiftStart = InputBox("Enter All Shift Start time ")
AllShiftEnd = InputBox("Enter All Shift End Time")
PMShiftStart = InputBox("Enter PM Shift Start Time")
PMShiftEnd = InputBox("Enter PM Shift End Time")
BackOfficeStart = InputBox("Enter BackOfficeStart Time ")
BackOfficeEnd = InputBox(" Enter BackOfficeEnd Time")
LunchBreakStart = InputBox(" Enter LunchBreakStart Time")
LunchBreakEnd = InputBox(" Enter LunchBreakEnd Time")
With Sheet1Departments.ComboBox2
.AddItem position
.AddItem comments
.AddItem DeptType
.AddItem AllShiftStart
.AddItem AllShiftEnd
.AddItem PMShiftStart
.AddItem PMShiftEnd
.AddItem BackOfficeStart
.AddItem BackOfficeEnd
.AddItem LunchBreakStart
.AddItem LunchBreakEnd
End With
Sheet1Departments.Cells(40, 2) = position ' this worked correctly for a position value of CEO
Sheet1Departments.Cells(40, 3) = comments
Sheet1Departments.Cells(40, 4) = "Type Admin "
Sheet1Departments.Cells(40, 5) = "All shift Start 7:55 am "
Sheet1Departments.Cells(40, 6) = "All shift End 3:55 pm "
Sheet1Departments.Cells(40, 7) = "PM Shift Start 3 pm "
Sheet1Departments.Cells(40, 8) = "PM Shift End 1 am "
Sheet1Departments.Cells(40, 9) = "Back Office Start 7 am "
Sheet1Departments.Cells(40, 10) = "Back Office End 2 am "
Sheet1Departments.Cells(40, 11) = "Lunch Break Start 12 noon "
Sheet1Departments.Cells(40, 12) = "Lunch Break End 1 pm "
' Just the first line is hard coded as a sample
Sheet1Departments.Cells(39, 2) = "PositionManager"
Sheet1Departments.Cells(39, 3) = "Comments Manger is responsible for management"
Sheet1Departments.Cells(39, 4) = "Type Admin "
Sheet1Departments.Cells(39, 5) = "All shift Start 7:55 am "
Sheet1Departments.Cells(39, 6) = "All shift End 3:55 pm "
Sheet1Departments.Cells(39, 7) = "PM Shift Start 3 pm "
Sheet1Departments.Cells(39, 8) = "PM Shift End 1 am "
Sheet1Departments.Cells(39, 9) = "Back Office Start 7 am "
Sheet1Departments.Cells(39, 10) = "Back Office End 2 am "
Sheet1Departments.Cells(39, 11) = "Lunch Break Start 12 noon "
Sheet1Departments.Cells(39, 12) = "Lunch Break End 1 pm "
' Sheet1Departments.Cells(39, ) = " "
End Sub
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.