Lost your smartphone – No worries now Google it.

Posted on by
Reply

Yeah today Google revealed a new feature of Google search- “To find your lost phone”.

All you got to do is type “find my phone” in the Google search box. Just that and yes google finds the location of your phone. You can also make it ring from the browser by clicking the “Ring” button. See below picture:

Google your lost phone - Techflames

Google your lost phone

I understand for this to work you need to have logged in to your computer’s browser with the same google account you use on your smartphone. Also your smartphone must be online. Else you cannot make it ring, just can see the last available location.

This would be very handy as I keep forgetting my phone in bathroom, bed, study… Now I can make it ring and find it easily.

I’ve used it and it works like a charm. Let me know what you think!

VBA Code to loop between sheets

Posted on by
Reply

Hi Folks these are some of the codes which might be useful when working with worksheets in a workbooks. Many times we end up adding too many sheets and would think it would be good if there was a way that excel provided more than 3 sheets everytime we create a new workbook. You can change this default property of excel. Go to Start/File—> Options—> Include this many sheets. By default its 3 change it to whatever number you need. Save the settings and next time you create a new workbook, it will have the desired number of sheets.

If wanted to do in with VBA the code to do the same is Application.SheetsInNewWorkbook = 5

VBA code to add a sheet at the end of all active sheets would be as below.

sub macro1()
Sheets.Add after:=Sheets(Sheets.Count)
Activesheet.name=”Details”
End sub

This will add a sheet and the 2nd part will count the number of sheets already in workbook and will place it AFTER that count. So the new sheet will be added at the end of all active sheets. And then we give it a name “Details”

To run this code go to Visual Basic Editor (From Excel hit ALT+F11). Create a new module( ALT+I+M) , paste the code and run it by pressing function key 5 (F5).

If you would want all the sheet names in a particular range then the below code should be of much use. Written with less complex stuff and easy to understand.

Sub listsheets()
Dim myrange As Range, st As Worksheet
Set myrange = Application.InputBox(“Select a cell where you want the list populated?”, “Select”, , , , , , 8)
myrange.Select
For Each st In ActiveWorkbook.Sheets
ActiveCell.Value = st.Name
ActiveCell.Offset(1, 0).Select
Next st
End Sub

To run this code go to Visual Basic Editor (From Excel hit ALT+F11). Create a new module( ALT+I+M) , paste the code and run it by pressing function key 5 (F5).

What this code does is it lets the user make the selection of range where he wants the list to be generated, the 3rd line in the code does it. Then we select that range what user has provided in the 4th line. And the next four lines is to run a loop to get all sheet names. Activecell.offset(1,0).select is used to move to next cell after entering a sheet name.

More on working with sheets would be creating a index summary with sheet names and hyperlinks on each sheet name, consolidating data from all sheets into one sheet, creating a search box in sheet1 so that when a criteria entered will filter data from all sheets and gets to Sheet1. All such requirements are done using the above logic.

Please add or let me know if you have any comments/suggestions. And do contact me for training on excel or any excel related automation on info@techflames.com

Thanks

Tags: vba classes in hyderabad, excel, macros, excel hyderabad, msoffice training in hyderabad

Protect Excel Sheet Dynamically

Posted on by
Reply

Hi All

I have been busy lately in training and automating some excel reporting stuff. Last week I was asked by one of my clients to protect a sheet dynamically. So that already entered information cannot be changed but should allow new rows to be entered. And when closed the newly added row should also be locked when re-open. Its difficult to explain the entire stuff in text here thus I recorded a small tutorial to explain it quickly as I felt it will be useful for many who might need it. Don’t mind the background noise for this one.

Bye.

Favorite shortcuts in Excel

Posted on by
Reply

If you spend half of your day working on excel then I strongly suggest to learn the excel shortcuts. They come very handy when you working on a deadline or just helps in making things faster.Though there are many shortcuts in excel I use these very often to work around in excel. Myself being a heavy excel user, I believe the list of shortcuts I use will be very helpful to everyone. Always when working with “Alt” key in any shortcut combination, the key should be pressed once and released and then the remaining combination should be typed.

  • Ctrl+N—> to add a new workbook
  • Shift+F11–>Add a new sheet.
  • Alt+E+L–> Delete a sheet
  • Alt+D+F+F—> Apply filter in a sheet
  • Alt + Space—> To expand dropdown
  • Ctrl+Tab–>Toggle in open sheets.
  • Ctrl+Pageup+Pagedown—>Toggle between the sheets up and down.
  • Ctrl+Home–> To move cursor to cell a1 in the sheet.
  • Ctrl+Shift+3–> To convert date to a better format.
  • Ctrl+Shift+4—> To convert number to currency format.
  • Shift+Space–> To select row (for column Ctrl+Space)
  • Ctrl+Shift++key—> To add a row or column
  • Ctrl+- key—> To delete a selected row or column
  • Ctrl+L—> To convert data to table (generally I do to format them properly)
  • Ctrl+~ —> to check all formulas in the sheet.
  • Alt+D+L—> Data validation.
  • Ctrl+1—> Get the format menu.
  • Alt+O+H+H–> Hide sheet.
  • Alt+O+H+U—> to unhide sheet.
  • Alt+O+H+R—> Rename a sheet.
  • ALT+E+S+V–> paste only values
  • CTRL+G–Goto Box.

Well there are numerous other shortcuts but these are the most that I use. Let me know what are the one’s which you use in Excel.

Shortcut to Highlight Cells

Posted on by
9

I started on a role to reconcile accounts and it involved manual reconciliation which could not be automated. Thus I use to check 2 sheets with an account number and if amounts match up in the 2 sheets then would have to highlight it with a color. But being keyboard savy it was a pain in excel to use the mouse and choose a color from the toolbar. Rather I wanted a keyboard shortcut which when pressed will highlight any cell and if pressed again will remove the highlight. After learning a little VBA I was able to make my own shortcut. Below is how I did it:

Open the visual basic editor by hitting ALT+F11 from excel. Create a new module (ALT+I+M) and add the below code.

Sub markcolor()
If Selection.Interior.ColorIndex = 6 Then
Selection.Interior.ColorIndex = 0
Else
Selection.Interior.ColorIndex = 6
End If
End Sub

Now switch back to excel and hit ALT+F8 which should bring the macro dialog box with the macro “markcolor” in the list. Just select it and click on “Options” which prompts to enter an alphabet for shortcut key. I enter the alphabet q in the box. And say Run.

That is it now go back to excel and when you hit CTRL+q the cell gets highlighted with yellow color and if pressed again it removes the color.

Note:

1) This shortcut will only work in the excel workbook where you have added the module. For advance users who want to have it available in the entire excel applications irrespective of any file selected, this code should be written in personal.xlsb file.

2) The color code for yellow is 6. If you need any other color then play around by changing this number.

Attaching the file with the code for reference. Happy Learning

 

 

Sharing Screen or Remote Desktop with ease : Teamviewer & Skype

Posted on by
8

Collaboration is a common word these days and so are virtual meetings / online trainings. Few years back it was hard to imagine employees working from their home but now with dazzling internet speeds travel to work is now avoidable. I can say internet is doing a great deal to reduce pollution and letting earth stay green.

A friend of mine had trouble installing an application and he needed my help to get it done. But he lives like 30 miles away and I am not that friendly to drive such a distance to help him. But did I disappoint him no ways!! I offered him my help over internet. There are 2 good ways to view/control a remote machine using internet and I used “TEAMVIEWER” on this occassion.

TEAMVIEWER: This is how it was done using teamviewer–

1) Me and my friend has to copy this url and paste it in browser and hit enter:  http://www.teamviewer.com/en/download/windows.aspx    �
2) Then we gotta hit the option which says “Download v7.0.12989″ { Remember v7.0… is the version. It can change after few days}
3) A dialog pops up to save or run the file. We hit run when prompted.
4) This downloads an “.exe” file and once the download is over hit “Run”. Accept the license agreement to launch teamviewer
5) On launch it throws a window with 2 sections a) Allow Remote Control and b) Control Remote Computer.
6) Now my friend gives me the random numbers (“Your Id” and “Password”) which is displayed in the first section ‘Allow Remote Control’. He sends me these details through mail or over phone.
7) I take the id and enter in the 2nd section ‘Control Remote Computer’ and hit ‘Connect to Partner’. It asks me the password and I enter the one which my friend provided. That is it!!

I am then able to see his screen and he can grant me permission to control his screen. I then fix any issues on his pc and install the app he needed thus saving money and environment.

The other option when someone would like to share his screen with others is using SKYPE. Skype is a popular platform to communicate over internet. I think the word skype is now added to Oxford dictionary. Ex: I will skype you tomorrow 9 PM. Anyways the easiest way to share screen while on a call is to clik the ‘+’ symbol on the skype menu bar and select “Share”. This is the best way when you are trying to give a demo of some product/explain something to a colleague or just having a casual talk on skype.

There are many other options which can be tried apart from Teamviewer and Skype like gotomeeting, logmein, webex, livemeeting but Teamviewer and Skype is all I have used since years and never felt the need of other platforms.

Hope you will use these channels and help reduce pollution on Earth.

Keep watching this space for more! Bye till then.

 

Creating Distribution List with VBA Excel

Posted on by
5

In the last post in Excel Category I talked about text functions and I posted a situation to create a distribution list from list of email ids in a column. There is no formula in excel which you can use to create a distribution list. But good news is that you can create one using VBA. Below is the code which would give a formula in excel to use for distribution list.

There are many ways to get this function written but below is one of the easiest way to understand the code. Paste the below code in any vba module and use it as a normal formula in excel.

********************

Function listcat(src_range As Range, diff As String) As String
‘src_range is range variable to allow range selection and diff is variable to store ‘differentiators
Dim final As String
‘variable called final to store the distribution list
Dim c As Range
For Each c In src_range
final = final & diff & c.Value
Next c
‘finally taking the clean distribution list to listcat
listcat = Right(final, Len(final) – 1)
End Function

*******************

That is it go back to excel and use this like a formula in excel, for ex: =listcat(B2:B9,”;”) where B2:B9 is the list of email ids which needs to be added and “;” is the separator.

Attached is the file for your reference with the code and formula hope you will find it useful.

Note: These days you can directly copy the entire values in the column and paste in outlook/other mail clients which will automatically create a list. But if in situations it doesn’t this is the best way.

 

 

Text functions in Ms-Excel: Left, Find and Concatenate

Posted on by
2

In the last post on text functions we saw trim and clean. For those whow work with lots of data analysis and specially if the data is coming from various sources he gotta use more than just trim to transform the data into meaningful information. One of the most useful function that I use is “Left”. Suppose you have “UHG212″ in a cell A1 and you need to extract the first 3 characters from the cell in A2 then you use this formula in A2:

=left(a1,3)

This will give you the first 3 characters from left and you can drag this formula to apply it for the entire column. To extract text from right you use the similar one =right(a1,3). Well that was easy but consider this situation you got a list of values like the below in cells B1 and B2 respectively.

EXTT_223

MYTTTY_443

In the above 2 values the text part is a course code and the numeric is its id number so I want to extract only the text part. (Of course you can use text to columns.) so to get this with a formula I need to know what is the position of underscore in each entry. So I know of a formula which would give me that which is =find(“_”,b1,1) which would return 5 for EXTT_23. So now to extract the text part out of it, you mix the left formula and the find which would be like this:

=left(b1,find(“_”,b1,1)-1)

This would give me the answer EXTT and how does that work? The find formula gives me the result 5 and I am substracting one from it to make it 4. Now using left function to get the 4 characters from left.

Just drag the formula to the remaining cells and it would give you the required course codes for each entry.

The data for the same with formulas is in this file (techflames_practice.xlsm), you can download and check it for your reference.

Before I wrap up I would also like to tell little about concatenate with an example. Suppose you’ve got info,@,techflaemes.com in 3 cells a1,b1,c1 respectively and you gotta join them to make the email id info@techflames.com how would you do it? 2 Ways one is using the formula in say cell d1 which is =concatenate(a1,b1,c1) which will give you the result else the direct way would be this formula =a1&b1&c1 which also gives you this result. Ok now I leave with this thought what if you have 100 emails in a1 to a100 cells  and you want to make a distribution list i.e. join them using a coma in between. Are you gonna write a lengthy formula like this =a1&a2&a3&a4…till &a100. Well for such things you got vba which can help you do this quickly. Will post the code in VBA section very soon.

Cya until next post.