Ali's Photography Space…

In last post we wrote all the codes needed to Manage the Authors.

In this part we will do all the coding needed to enter New Books and Edit or Delete anyone we select, in Classifications and Authors we were dealing with one attribute, but here with Books Manage form we have several pieces of information to collect from the user such as [Book Title, Book Author, Book Classification, Publish date, Notes, Book Language ]. So first let’s design the form..

1. Open the Books sheet
2. Change the color of the range(B6:E24)
3. In Cell B6 write “Select a Book to Edit or Delete”, Color and format it as you want.

4. In Cell H6 write “Books Form”, Color and format it as you want.

5. Color and format the Range (B6:L26) as you want.
6. Create three rectangular shapes as New, Delete, and Save buttons.
7. Create another three small rectangular shapes write “+” inside them.
5. We need to create a ListBox name it “books_ListBox 4”.
6. Arrange everything as in the image.

Now move to the “setting” sheet and write the following: B4:Books, B5:current_book, C5:1, B6:mode, C6: edit.

Then in the “Data” Sheet starting from A1 write the following:
A1:Books Data, B1:BookTitle, C1:book_author, D1:class, E1:Published, F1:Note, G1:lang
A2:ID, B2:Title, C2:Author, D2:Class, E2:Published, F2:Note, G2:language

CODING:

To copy the list of the Books we have into the books_ListBox 4 we create:
1.1 From the Menu go to Formulas and click on Name Manager.
1.2 From the pop-up screen click on New, then write books_list in Name, and =OFFSET(Data!$B$3,,,COUNTA(Data!$B:$B)) in Refers to.

2.1. Select books_ListBox 4 on the Books sheet.
2.2. Right-click the mouse, and select FormatControl.
2.3. Goto Control Tab, and in Input range write: books_list, and in Cell link write Setting!$C$5 then press OK.

Now the listBox will contain the Books we have in the Books table in Data Sheet. (If we have any Data there)
3. Data Validation: We need to create Data Validation-list in the cells K12: for Authors list, K14: for Classifications list and K18: for Language list.
1. Select K12, from Data-menu click on Data-validation, select List then in the source type this: =OFFSET(Data!$K$3,,,COUNTA(Data!$K:$K))
2. Select K14, from Data-menu click on Data-validation, select List then in the source type this: =OFFSET(Data!$P$3,,,COUNTA(Data!$P:$P))
3. Select K18, from Data-menu click on Data-validation, select List then in the source type this: =OFFSET(Data!$M$3,,,COUNTA(Data!$M:$M))

book_new_Click() In this function we will Clear all cells [k8, k10,k12,k14,k16,k18, K20] also the note_TextBox1 and will change the cell C6 in Setting Sheet to “new”. Here is the code..

now in the Books sheet select the button “New” we create and assign the “book_new_Click” macro to it.

book_save_Click() The Save function will have tow parts, if the user click on New then we will have:

Sheets(“setting”).Range(“C6”).Value = “new” in this case we will copy all the Book Data from Range(“K8”),Range(“K10”),Range(“K12”),Range(“K14”),Range(“K16”),Range(“K18”) and the value of the note_TextBox1 to the Data sheet under Boos Table, then will empty all the range in the book form, also will pop-up a MsgBox ” One New Book has been Added.”.

if Sheets(“setting”).Range(“C6”).Value = “edit” in this case we will get the current selected book location by selected_book = Sheets(“setting”).Range(“C5”).Value + 2 then re-copy the book data from the form to the Book Table in the Data Sheet and pop-up MsgBox ” One Book Data has been Updated.”.

In Books form we have also three + buttons next to Author, Classification and Language cells, if the user did not find say the Author in the list then he/she can add new one by clicking on the +

Edit Book Data: The user can select any Book from the Book List on the left-hand list then it’s Data will show-up in the form, the user then can change any of the Book-Data and press on Save.

book_delete_Click() The user will select the Book to de deleted then click on “Delete” button, a massage will pop-up to make sure that Book will be DELETED, if the user confirm the cation (press OK) we will run this line of code:

‘To get the book row number.
selected_book = Sheets(“setting”).Range(“C5”).Value + 2
‘To delete the book row
Sheets(“data”).Range(“A” & selected_book & “:G” & selected_book).Delete Shift:=xlUp

Pressing the (+) button: We have three (+) buttons in this form to add New Authors, Classifications, and Languages if not exist in the list; for example, if the user couldn’t find the Author of the Book (he/she want to enter to the system) pressing the (+) will pop-up a dialog box to Enter New Author and the same for classification and Language. Here is the code..

End of Part-4

Recap this part:
1. We Create the Books header Tabke.
2. We Create a form to collect the Books from the user.
3. We Create the Books ListBox.
4. We wrote the VBA code to Save, Delete, and Create New Book also to retrieve the Books information into Books ListBox.
5. We let the user to Enter New Author, classification, Language into the system through a dialog box.

:: Library System with Excel ::

To Download EXCEL (.xlsm) files Click-Here