Archive

Posts Tagged ‘excel’

Excel, VBA Codes and Formulas-1

March 8, 2020 7 comments

Learn To : In Excel – Drop Down List in a cell.

I start working on a project for a friend, he has some requests to be done on a MS-Excel, so I thought why not to write several posts about MS-Excel capabilities.

MS-Excel is a great application to be used, I will not write to praise the program, but will jump to coding. In Excel we can use dozens of built in ready to use tools and Formulas. Also MS-Excel has a very good, easy to use and learn programming language called VBA (Visual Basic for Application) which with simple code we can perform some tasks and create Menus, buttons and functions that runs in background.

Starting from this post we will use some built-in functions and VBA’s one that I am using in my Excel files.
So let’s say we have a table that has a column called “Pay-Method”, the value we save in this column always is one of three: “Cash, Card, Cheque” we don’t want the user to type it every time, but to select it from a list.

To do this we can use an static approach or a Dynamic approach.

Static Approach: if we have a specific answers that can’t be more like (Yes,No) or as our example above (Cash, Card, Cheque) or similar cases then we can use an static approach. So First Select First cell in the Table, from the menu we will select “Data” then “Data Validation” and select Data Validation.



Now,

  • From the Allow box, we select List.
  • Then in Source box, we need to type the items we want to appear in each cell as a drop-down list separated by a comma. Cash, Card, Cheque
  • We need to check [Ignore Blank and In-cell Dropdown] Then Click OK.

Now we will have a small arrow on the first cell in our Table, to copy the List to other Cells (Beneath) hold the small DOT and Drag it down.

And Now we are DONE, we just create a Dropdown List in all “Pay-Method” Column in the table..



Dynamic Approach: If we have a list of items and this list is growing (We may add more items ti it), such as Fruit list in a grocery, then we will use the Dynamic drop-down list.
In this case I prefer to use a new Excel Tab call it “Setting”, so we will create one. On that Tab create a Table call it “Fruit” and list all the fruits you want to be appear in the drop-down cell list (In this example I will list down F1, F2, F3, F4, F5), Select the table and from Menu select “Format as Table” and just chose any format you want. Here is a screen shot.

Here is a screen shot

Move the Mouse next to table head until you get small Arrow, and give it a name. as in here..



Now go-back to our main Table and using the same way we did in the Static Approach (from the menu we will select “Data” then “Data Validation” and select Data Validation.) AND

  • From the Allow box, we select List.
  • Then in Source box, we need to type the Name we select for our table “F-Table_1” for the fruit table
  • We need to check [Ignore Blank and In-cell Dropdown] Then Click OK.



Done … Now if we add new Fruit to the list “F6” it will appear in our Drop-Down cell list.

🙂 Have Fun ..



Follow me on Twitter..




By: Ali Radwani




My Fitbit Data

October 22, 2019 Leave a comment


Solving My Fitbit data Problem
Subject: VBA code to combine Fitbit excel files into one

I purchase a Fitbit Alta HR in 2017, and since then I wear it and never take it off unless for charging. Fitbit Alta HR is a very nice slim device, but unfortunately there was no way to upload my previous data from my old device to my Fitbit, this was not the only issue with Fitbit data, now; after three years still we can’t download Fitbit data in one file, if you want to do this (each time) you need to send a request to Fitbit team and they will prepare the file for you!! Instead, Fitbit site allow you to download your data as month by month, in my case I will have almost 32 files.

Solving the Problem: After downloading the excel files and look at them, I decide to write a code to help me combine all the current 32 files and any coming once into one data file. First I start thinking to use Python to do this task, but after second thought I will use the Excel file and VBA macro coding to do it.
Here in coming paragraph I will post about the general idea and some codes that i use.

General Idea: I will use same structure of Fitbit file and name it as “fitbit_All_data_ali”, in this file we will create new tab and name it Main. In the Main tab we will create several buttons using excel developer tools and will write the macro VBA code for each task we need to operate.

Tabs in our file: Main: Contain buttons and summary about my data.
Body, Food, Sleep, Activities and Food Log. Food Log tab will store the data such as calories, fibers, fat and so-on., all those tabs will be filled with a copied data from each Fitbit data file.

Here are some VBA codes that I use and what it’s purpose .

Code:
‘ Get current path.
the_path = Application.ActiveWorkbook.Path
The path on the current excel file.
Code:
the_All_Data_file = ThisWorkbook.Name
Get current excel file name
Code:
Workbooks.Open Filename:=thepath + my_source_Filename
Open a file
Code:
Windows(my_source_Filename).Activate
Sheets(“Foods”).Select
Range(“A2:B31”).Select
Selection.Copy
Goto fitbit file, goto food sheet, select the data copy it.
Code:
Application.CutCopyMode = False
Workbooks(my_source_Filename).Close SaveChanges:=False
Close an open excel file.
Code:
Range(“A3”).Select
Selection.EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
To insert a black Row
Code:
sname = ActiveSheet.Name
Get current sheet name
Code:
Function go_next_sheet() As String

‘ This code will go to next sheet if there is one, if not will return ‘last’
go_next_sheet = “no”
Dim sht As Worksheet
Set sht = ActiveSheet

On Error Resume Next

If sht.Next.Visible xlSheetVisible Then
If Err 0 Then
go_next_sheet = “last”
End If

Set sht = sht.Next
End If

sht.Next.Activate
On Error Resume Next

End Function

if there is no more tabs or sheets, function will return “last”


Final Results: After i run the code, I have an Excel file contain all my Fitbit data in one place. Mission Accomplished



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




Follow me on Twitter..