Archive
Library System with Excel -P4
Learning :Excel formulas and VBA Cods
Subject: To Develop a Library System with Excel
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..
‘ Clear all cells.
Sheets(“Books”).Range(“K8”).Value = “” ‘ID/number
Sheets(“Books”).Range(“K10”).Value = “” ‘Title
Sheets(“Books”).Range(“K12”).Value = “” ‘Author name
Sheets(“Books”).Range(“K14”).Value = “” ‘classification
Sheets(“Books”).Range(“K16”).Value = “” ‘published date
Sheets(“Books”).Range(“K18”).Value = “” ‘language
Sheets(“Books”).Range(“K20”).Value = “” ‘note
Sheets(“Books”).note_TextBox1 = “”
‘ change book mode to “new”
Sheets(“setting”).Range(“c6”).Value = “new”
Sheets(“Books”).Range(“K8”).Select
End Sub
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..
Sheets(“Books”).Range(“K12”).Value = “”
new_author_name = InputBox(“Enter a New Author Name and Click OK:”, “:: New Author :: “)
‘if we add new author then save it.
If new_author_name > “” Then
‘ Get next empty row
next_row = Sheets(“Data”).Range(“K” & Rows.Count).End(xlUp).Offset(1).Row
Sheets(“Data”).Range(“K” & next_row) = new_author_name
Sheets(“Books”).Range(“K12”).Value = new_author_name ‘Author name
Sheets(“setting”).Range(“F6”).Value = “still”
‘ To Sort the Authors list
next_row = Sheets(“Data”).Range(“K” & Rows.Count).End(xlUp).Offset(1).Row
Sheets(“Data”).Range(“K3:K” & next_row – 1).SortSpecial SortMethod:=xlPinYin
End If
Sheets(“Books”).Range(“K12”).Select
End Sub
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 ::
| Part 1 | Part 2 | Part 3 | Part 4 | Part 5 |
To Download EXCEL (.xlsm) files Click-Here
By: Ali Radwani
Python: Spirograph
Learning : Python
Subject: Writing Python codes to generate Spirograph Art
Writing codes to draw always funny time for me, playing around numbers, changing attributes to get something new and changing it again .. and again ..
In this post, we will write a code to draw some spirograph shapes, easy and shrort code will do the job. So lets Start ..
We will use Python Library turtle to draw, and will write one Function call it def draw_it(h,sz,ang) will take three arguments: h:number of heads, sz: size, ang: angle, then we will call this function to draw our Spirograph Art.
Code:
First we will set-up the turtle:
# turtle set-up
import turtle
t = turtle.Turtle()
t.shape("dot")
t.speed(0)
t.hideturtle()
Then here is the main Function to draw our graphs
# draw_it() function
def draw_it(h,sz,ang) :
c = 0
while True :
for i in range (h) :
t.forward(sz)
t.right(360/h)
t.right(ang)
c +=1
if c >=360/ang :
break
Then we call the function and pass the parameters, I tried several combinations and will include them in the source file in Download section. Here are some out-puts.
| Calling: t.pencolor(‘lightgray’) draw_it(19,19,19) t.pencolor(‘gray’) draw_it(17,17,17) t.pencolor(‘black’) draw_it(15,15,15) 
|
|
 |
Hope you enjoy, have fun and change the numbers to get new shapes ..
To Download my Python code (.py) files Click-Here
By: Ali Radwani
Library System with Excel -P2
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:
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:
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
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
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
By: Ali Radwani
Mushroom House
Here is another attempt to improve my skills in sketching and Coloring using water color. Mushroom House.
Another SketcheBook
After my 6th Sketchebooks I start creating sketchebooks for my self, cutting the A4 papers, punch or glue them together then creating the cover and Sketch over it, other sketchbooks are purchased online then again I draw/sketch on it’s cover. Sample of what I made here ..
Sketch Books 12, 13 Here
Sketch Books 22, 23 Here
This one here is my 42nd Sketchebook Cover, I purchased this from AliExpress site, verey nice and small but the papers are very thin and the colors appear on the other side of the page, over all, they are doing the job.
|
More sketches are available in My Sketchbooks Page..
1Hour1Sketch challenge
While searching the Twitter I found a challenge called “1hour1sketch” posting a theme or a photo and asking however want to participate and doing/sketching around the idea, so I thought I can try it and get a subject to draw. Here is my sketch for this week..
I am using galaxy Note9 and Samsung Now app.
|
By: Ali Radwani
Daily Digital Sketch
Here is the daily-sketch for today. I am using Galaxy Note 9 and Samsung Note Application for drawing and Coloring..
more on my Twitter here H.ta3kees
|
|
By: Ali Radwani
Python: Shares Speculation System – Part 7
Learning : Python, DataBase, SQL, SQlite3
Subject: Plan, Design and Build a Shares Speculation System
Project Card:
Project Name: Shares Speculation System
By: Ali
Date: 2.7.2020
Version: V01-2.7.2020
In this Part we will write the Function to Delete a Transaction from the System, and will write the last part of show_all_Transaction function.
First we will write a Function to Delete a Transaction, we have two types of Transactions “Buying” and “Selling” so the user will select the Transaction Type to delete.
1. Buying Transaction: If the user select to Delete a Buying Transaction, we will call show_all function to display all the Buying Transactions and the ask the user to Enter the ID of the record to be Deleted. Here is the Code ..
 |
Then we will print-out the Selected Transaction Details and ask the user to Confirm the Deleting action by Entering ‘Y’. After Confirming we need to perform or to Aplay three parts of code:
1. Deleting the Buying Transaction from Buying Table (buy_t_table).
2. Deleting the Buying Record from the Budget Table.
3. Updating the Share Basket Table (s_basket).
For the “Update the Share Basket Table (s_basket).” we need to Subtract the amount of the shares from the Share Basket.
Here is the code for all three action..
 |
The Same actions will be taken if the user select to Delete a Selling
transaction. So if the user select “2. Selling Transaction:” we will call show_all function to display all the Selling Transactions and the ask the user to Enter the ID of the record to be Deleted and will display the Transaction Details. Here is the Code ..
 |
And will waite for the user to Confirm the Deleting action, then again three blocks of code will Delete and update records in our system, here are the code ..
 |
Now we finish the def del_trans(): Function to Delete a Buying or Selling Transaction and will work on the last part of def show_all_trans (inside = ‘No’, show=’3′): to Show/Display both Buying and Selling Transactions in one table format. To do so First we will RUN two SQL commands to ‘fetchall’ Buying and Selling Transaction… Here is the code ..
# Get All Sell Transactions Order by id desc
c.execute (" select * from sell_t_table where st_id > 0 order by st_id desc")
show_s_trans = c.fetchall()
# Get All buy Transactions Order by id desc
c.execute (" select * from buy_t_table where bt_id > 0 order by bt_id desc")
show_b_trans = c.fetchall()
Then we print-out the Table header as the following line:
print(“\n”,” “*11,”{:<9}{:<13}{:<30}{:<16}{:<11}{:<15}{}".format('ID','Date','Share Name','SAmount','Price','Cost','Income'))
Then using a For loop we will print-out the transactions in same format of the table header .. Here is the full Code
 |
Last thing we will print the Totals of Investment, Incomes and the Net Profit.
print(" "*73,'Total Investment is: {:,}'.format(total_inv))
print(" "*76,'Total Incomes is: {:,}'.format(total_inc))
print(" "*75,"-"*30,"\n"," "*75,' Net Profit is: {:,} '.format(total_inc - total_inv))
We Done with this Part ..
Coming Up: In the Next post we will write the Function to Edit a Transaction.
[NOTES]
1. Part-1 has no code file.
2. We are applying some basic Validations on some part of the code, and assuming that the user will not enter a messy data.
3. This Application Purpose for Saving Transactions and NOT for Desetion Making and Dose’t Have any type of AI or ML Model to Predict the Prices and/or Giving Sugestions on Buying or Selling Shares.
:: Shares Speculation System ::
| Part 1 | Part 2 | Part 3 | Part 4 | Part 5 |
| Part 6 | Part 7 | Part 8 |
All the code are available ..
To Download my Python code (.py) files Click-Here
By: Ali Radwani
Sketch from One Year Back
This is an Elephant sketch from One #year ago i use a pencile then black ink pen.
|
To The Freedom
Taking pictures is not my main daily practices, but when i start playing with my camera, i really enjoy my self.
Thanks for visiting my Space..
