Archive

Archive for November 15, 2020

Library System with Excel -P2

November 15, 2020 2 comments


Learning :Excel formulas and VBA Cods
Subject: To Develop a Library System with Excel

In last post we create the sheets and work on the Menus.

In this part we will do all the coding needed to enter the classifications and Edit or Delete anyone we select, so first we will go to the “setting” sheet and will write the following: B4:Books, E4:Authors, H4:Classification as in the image.



Now in the “Data” sheets, we create a table with “classifications” header on range P2, then enter any classification such as “art” as in image.


Now, we move to “Classification” Sheet and do the following:
1. In Cell B6 write “Select a Classification to Edit or Delete”, Color and format it as you want.
2. In Cell H6 write “Classifications Form”, Color and format it as you want.

3. Color and format the Range (B7:F20) and the Range (H7:M12) as you want.
4. Create three rectangular shape as New, Delete, and Save buttons,
5. We need to create a ListBox name it “class_ListBox1”.
6. Arrange everything as in the image.


CODING:
1. To copy the list of the classification we have into the ListBox:
1.1. Select the ListBox.
1.2. Right-click the mouse, and select FormatControl.
1.3. Goto Control Tab, and in Input range write: class_list, and in Cell link write setting!$I$5 then press OK.

Now the listBox will contain the Classifications we have in the classification list in Data Sheep. … [SEE THE IMAGE]



ListBox Code:
Now we will write a three lines of code to take action when we select any things in this box, so select the ListBox, click right-mouse-button, select “Assinge Macro, then the VBA application will start and write the foloing code:

Sub class_ListBox1_Change()

selected_class = Sheets(“Setting”).Range(“I5”).Value + 2
Sheets(“classifications”).Range(“K8”).Value = Sheets(“data”).Range(“P” & selected_class)
Sheets(“Setting”).Range(“I6”).Value = “edit”

End Sub


Buttons Codes:
1. Select the rectangular shape named “New”, click right-mouse-button, select “Assinge Macro, then the VBA application will start and we
will write this code:

Sub class_new_Click()
Sheets(“classifications”).Range(“k8”).ClearContents
Sheets(“Setting”).Range(“I6”).Value = “new”
End Sub


2. While the VBA application is on, we will write all the codes we need for Edit and Delete buttons and then will assign the macros:
class_delete_Click

Sub class_delete_Click()

If Not Sheets(“setting”).Range(“I5”).Value Then

End Sub
End If

answer = MsgBox(“Are you sure you want to DELETE this Classification?.”, vbQuestion + vbYesNo)
If answer = vbYes Then
current_select = Sheets(“setting”).Range(“I5”).Value + 2
Sheets(“Data”).Range(“P” & current_select).ClearContents

next_row = Sheets(“Data”).Range(“P” & Rows.Count).End(xlUp).Offset(1).Row
Sheets(“Data”).Range(“P3:P” & next_row – 1).SortSpecial SortMethod:=xlPinYin
class_ListBox1_Change

MsgBox “One Classification has been Deleted..”
Else
MsgBox “OK, Nothing will be changed.”
End If

End Sub



class_save_Click

Sub class_save_Click()

If Sheets(“classifications”).Range(“K8”).Value = Empty Then
MsgBox “There is no Classification to Save.”
Exit Sub

End If

If Sheets(“Setting”).Range(“I6”).Value = “new” Then

‘ Get next empty row
next_row = Sheets(“Data”).Range(“P” & Rows.Count).End(xlUp).Offset(1).Row

‘ copy the new classification to the data-table
Sheets(“Classifications”).Range(“K8”) = StrConv(Sheets(“Classifications”).Range(“K8”), vbProperCase)
Sheets(“Data”).Range(“P” & next_row).Value = Sheets(“Classifications”).Range(“K8”)

‘ Empty the form
Sheets(“classifications”).Range(“k8″).ClearContents
MsgBox ” One New Classification Saved.”

‘ To Sort the classifications
next_row = Sheets(“Data”).Range(“P” & Rows.Count).End(xlUp).Offset(1).Row
Sheets(“Data”).Range(“P3:P” & next_row – 1).SortSpecial SortMethod:=xlPinYin
class_ListBox1_Change
Exit Sub

End If

If Sheets(“setting”).Range(“I6”).Value = “edit” Then

selected_class = Sheets(“Setting”).Range(“I5”).Value + 2
Sheets(“Classifications”).Range(“K8”) = StrConv(Sheets(“Classifications”).Range(“K8”), vbProperCase)
Sheets(“data”).Range(“P” & selected_class) = Sheets(“classifications”).Range(“K8″).Value
MsgBox ” One Classification Changed.”

‘ Sort the classifications
next_row = Sheets(“Data”).Range(“P” & Rows.Count).End(xlUp).Offset(1).Row
Sheets(“Data”).Range(“P3:P” & next_row).SortSpecial SortMethod:=xlPinYin

class_ListBox1_Change
Exit Sub
End If

End Sub


In the Save code we check if the K8 Cell is empty before we save it, and then we also re-sort the data in the ListBox. Here is a part of the code ..



Recap this part:
1. We Create a classification list.
2. We Create a form to collect the classifications from the user.
3. We Create the classification ListBox.
4. We wrote the VBA code to Save, Delete, and Create New classification also to retrieve the classifications into classification ListBox.


:: Library System with Excel ::

Part 1 Part 2 Part 3 Part 4



To Download EXCEL (.xlsm) files Click-Here




Follow me on Twitter..



By: Ali Radwani




Categories: excel, Learning, Lesson, Problem, Projects/Experiments Tags: , , , , , , , , ,