Archive

Posts Tagged ‘ahradwani’

Library System with Excel -P1

November 11, 2020 2 comments


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

Someone asked if we can do the Library Management system using Excel, so in the coming several posts we will try to develop an Excel File to store our Books. So I will stop the CRM Project and will work on Library System, we will use MS-EXCEL and VBA code to create a simple spreadsheet looking after our Books.

To start, do the following:
1. We will create an empty Excel file and call it “my Library System” and save it as “Excel Macro-Enabled Workbook.xlsm”
2. Create 7 Tabs(Sheets), name them as:
Menu, Books, Authors, Classifications, Data, Setting, Summary

In this post (Part-1), we will work on the Menu sheet, I will not concern about the themes and colors or fonts all this is back to each user to do formating as needed. So let’s begin with creating four buttons using the insert – shapes Rectangle: Rounded Corners.

We need to create four Rectangle: Rounded Corners, align them as you want and use any theme color, then give each a Captions as in the coming image..

Now, select the Books Button and with Right-Mouse-button select “Assign Macro” and from the new Assign Macro pop-up screen click on New.

This will launch the VBA Application with an open window to write our code, here is the code that if the user clicks on ‘SAY’: Books-Button the Books Sheet will be selected. In coming image, you will find the codes for all buttons we have in the Menu.

Also, we create a code to take us back to the Menu sheets and we call the button MENU, we will add this Button to all sheets we have.


    Recap this part:

  • 1. We Create an Excel file.
  • 2. We Create 7 Sheets and named them.
  • 3. We Create Buttons for the Menu Sheet.
  • 4. We Create the Home Button.
  • 5. We wrote the VBA code so we /the user can navigate thrugh the system.



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




Excel Simple CRM System P1

November 9, 2020 Leave a comment


Learning : Excel Simple CRM System
Subject: Create Excel Form, Dynamic Name Range and VBA Code to build a simple CRM system.

In this short project we will learn how to create a simple CRM system and and will build forms to collect needed data and store it in tables.

Case Study:
We want to create a simple CRM system to collect data about our employees such as [Name, Department, Salary], we will keep the system (Forms and codes as simple as we can). In this part, we will work on fomrs and code to collect the Employee data.

First, we will open an Excel file, and will create Three Tabs (Sheets), and will name them [Form, Data, Table]. As in figure 1

As in figure 1


Now try to create the formes for Employee and another for Departments (Download the Excel file here).

Then in the Data Sheet starting from A1 create a table as A1: Name, B1: Department, C1: Salary, and in the F1 we will create a table named Department List. As in figure 2

figure 2

and enter one row of data as in figure 2. Then we also need to create a Dynamic Name Range, for both employee name and Department List, to do so we:
1. Go to Data Sheet.
2. Select the Employee table that we create.
3. in the “NameBox” just give the table a name as “emp”.

Do the same to the Department List table dep_list, we will use these names later.

Coding: Now we will write the VBA code to make a list of Departments we have. To do so we will select the button of SAVE under the Department Form, Click the Right mouse button, and select Assign Macro, then select New and OK, after that the VBA application will start. We will write this code:

Sub save_new_dep()

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

Sheets(“data”).Range(“F” & next_row).Value = Sheets(“form”).Range(“J7”)
Sheets(“form”).Range(“J7”).ClearContents

End Sub

figuer 4

In this line
next_row = Sheets(“Data”).Range(“F” & Rows.Count).End(xlUp).Offset(1).Row we will get the next empty row in the department list.

in this line Sheets(“data”).Range(“F” & next_row).Value = Sheets(“form”).Range(“J7”) we will copy the date we just enter as a New Department to the Data Sheet Depatrtment List. Then with Sheets(“form”).Range(“J7”).ClearContents we will clear the content.

So now we can enter some department names: IT, Fin, Economy

We will do the same thing for the Employee Form, so with an open VBA application write the following code. as in figure 5


Sub save_new_emp()

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

‘ Copy the Data
Sheets(“data”).Range(“A” & next_row).Value = Sheets(“form”).Range(“D7”)
Sheets(“data”).Range(“B” & next_row).Value = Sheets(“form”).Range(“D8”)
Sheets(“data”).Range(“C” & next_row).Value = Sheets(“form”).Range(“D9”)

‘ Clear the Form
Sheets(“form”).Range(“D7”).ClearContents
Sheets(“form”).Range(“D8”).ClearContents
Sheets(“form”).Range(“D9”).ClearContents

End Sub

figure 5

The first line of the code is to get the next empty line in the Employee table, then we will use it to copy the data from the form to the data table. In my form, the Name is in D7, the Department in D8, and the Salary in D9. here is the code line for the name: Sheets(“data”).Range(“A” & next_row).Value = Sheets(“form”).Range(“D7”), then we clear the form as we did with the Department form. In the Employee form (Range”D9″) we need to make it as a DropDown list of all Departments we have so we can select from the list, to do that follow the coming steps ..
1. Select Cell D9.
2. From the Menu Select Data then Data Validation, a popup box will apear as in figure 6, do as shown, here we will use the Dynamic Name range we create “dep_list” as a source. figure 6

figure 6



At this point we have two Forms, one for the Department List and one for the Employee (Name, Department, Salary), both forms has SAVE buttons, the Data will be saved in the Data Sheet



To Download Excel files Click-Here




Follow me on Twitter..




By: Ali Radwani




Another SketcheBook

October 13, 2020 Leave a comment

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

September 26, 2020 Leave a comment

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.


Follow me on Twitter..

By: Ali Radwani

Sketch from One Year Back

August 5, 2020 Leave a comment

This is an Elephant sketch from One #year ago i use a pencile then black ink pen.

Follow me on Twitter..

To The Freedom

August 3, 2020 Leave a comment

Another sketche I woke on with watercolor.

Follow me on Twitter

Lily Flower Drawing


Away from Coding and Python projects, drawing is my best way to relax, along with coffee and almost 15min of sketching with pencil then ink-ed it, here is the Lily.

Follow Me on Twitter

Coloring Some Sketches


Again I just pecked one of my sketches and color it using my galaxy Note9 mobile... Usually I use pencil then ink-ed the sketch, some time I color it with Watercolor or Promarker. Just to kill the time, I load one of them to the “Autodesk Sketchbook” App on my Galax Note9 and color it. The sketche is from my SKB 39 ..and here it is . .

More sketches on my-Sketch page.

Sketching and Coloring a Shark


Some time ago I stope publishing my Sketches on my blog or my Twitter account, but this doesn’t mean I stop sketching, I am sketching to improve my skills and clear my mind. Usually I use pencil then ink-ed, some time I color it with Watercolor or Promarker. Today I load one of them to the “Autodesk Sketchbook” App on my Galax Note9 and color it. The sketche is from my SKB 39 ..and here it is . .

More sketches here .. My Sketches Page.

Ali Radwani,

Python and Lindenmayer System – P2

November 3, 2019 1 comment


Learning : Lindenmayer System P2
Subject: Drawing with python using L-System

In the first part of Lindenmayer System L-System post (Click to Read) we had wrote two functions: one to generate the pattern based on the variables and roles, and one to draw lines and rotate based on the pattern we have.

In this part I will post images of what Art we can generate from L-System
the codes will be the L-system that generate the patterns, so the code will include: the Rules, Angle (Right, Left) Iteration and Starting Variable.


L-System: Koch Curve

L-System: Minkowski Sausage

L-System: … but here the Iteration is: 3

L-System: Again … but here the Iteration is: 3

L-System: Square Sierpinski

L-System: Sierpinski Arrowhead.

L-system: Dragon Curve

L-System: Koch Snowflake

L-System:

L-System:


The possibilities to generate the putters and therefore drawing the output is endless, any slightly changes in the iterations or rotation (+ -) angles will take all output to a new levels. In the coming post, I will use the L-system to generate fractal tree and see what we can get from there.



To Download my Python code (.py) files Click-Here




Follow me on Twitter..