Ali's Photography Space…

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

In this part we will do all the coding needed to enter the Authors and Edit or Delete anyone we select, so first we will go to the “setting” sheet and will write the following: E5:current_author, F5:1, E6:mode, F6: edit as in the image.

Now in the “Data” sheets, we create a table with “Author Name” header on range K2, then enter any Autor name such as “Albert” as in the image.

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

3. Color and format the Range (B6:E19) and the Range (H6:K13) as you want.
4. Create three rectangular shapes as New, Delete, and Save buttons.
5. We need to create a ListBox name it “authors_ListBox1”.
6. Arrange everything as in the image.

CODING:
To copy the list of the Authors we have into the ListBox:
1.1 From the Menu goto Formulas and click on Name Manager

1.2 From the pop-up screen click on New, then write author_list in Name, and =OFFSET(Data!$K$3,,,COUNTA(Data!$K:$K)) in Refers to [as in image]

2.1. Select the ListBox in the Authors sheet.
2.2. Right-click the mouse, and select FormatControl.
2.3. Goto Control Tab, and in Input range write: author_list, and in Cell link write Setting!$F$5 then press OK.

Now the listBox will contain the Authors we have in the Author 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:

Buttons Codes:
1. While in Authors sheet, 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:

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:
author_delete_Click

author_save_Click

In the Save code we re-sort the data in the ListBox. Here is a part of the code we use to do so:
‘ Sort the Authors Name
next_row = Sheets(“Data”).Range(“K” & Rows.Count).End(xlUp).Offset(1).Row
Sheets(“Data”).Range(“K3:K” & next_row).SortSpecial SortMethod:=xlPinYin
authors_ListBox1_Change


New we can assign the macros we just creates to the buttons we have (Delete and Save).

End of Part-3

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

:: Library System with Excel ::

To Download EXCEL (.xlsm) files Click-Here