Sunday 31 December 2017

How to validate a textbox while entering data in VBA | Data validation in VBA user forms

You can add below code on the event of textbox_keypress, textbox_enter, etc to validate the entry while user enters the data into the textbox.


Function ValidateTextboxForNumbersOnly(ByRef tb As MSForms.TextBox)
'Purpose : To validate and clear data in text box if value is not numeric
    If IsNumeric(tb.Value) = False Then tb.Value = ""
End Function

Friday 1 December 2017

How to unhide all sheets in an excel workbook / Show all sheets in one click / Unhide veryhidden sheets

Sometimes when we create larger reports in Excel, we will hide few sheets where we entered raw data or controls or calculations.

It's a tedious task to right click and unhide all the sheets one by one.

Sometimes, we will have sheets which are hidden from VBA IDE also which cannot be unhidden from Excel GUI.

You can unhide all the sheets in an excel workbook with a small piece of code below.


Sub UnHideSheets()
Dim Sht As Worksheet
For Each Sht In ThisWorkbook.Sheets
    Sht.Visible = xlSheetVisible
Next Sht
End Sub

Thursday 5 October 2017

View & work with same Excel file in more that 2 places at a time / Two windows at a time

Sometimes when we work on Excel, it's required to do some comparisons, copy paste, analysis on the same workbook at same time

You can do this easily following below steps

1. Goto View Ribbon 

2. Find Windows section

3. Click on New window

You can see two instances of same workbook is open now.

Note : For more easy usage, Make workbook window in "Restore Mode" and reduce the size of windows to arrange in your desktop as you wish to work, so you can see and use both instances at same time.

Wednesday 16 August 2017

Power Point ShortCuts

POWERPOINT 

ActionKeystroke
Document actions
Open a presentationCTRL+O
New presentationCTRL+N
Save AsF12
SaveCTRL+S
PrintCTRL+P
HelpF1
Presentation actions
Begin slide showF5
Next slideENTER or
Down arrow key
Previous slideBACKSPACE or
Up arrow key
Activate pen toolCTRL+P
Erase pen strokesE
Deactivate pen toolCTRL+A
Show/Hide black screenB
Show/Hide white screenW
Show/Hide pointer & buttonA
End slide showESC

ActionKeystroke
Formatting
Select allCTRL+A
CopyCTRL+C
CutCTRL+X
PasteCTRL+V
UndoCTRL+Z
RedoCTRL+Y
BoldCTRL+B
ItalicsCTRL+I
UnderlineCTRL+U
Left justifiedCTRL+L
Center justifiedCTRL+E
Right justifiedCTRL+R
Promote list itemALT+SHIFT+Left arrow
Demote list itemALT+SHIFT+Right arrow or TAB
Editing
FindCTRL+F
ReplaceCTRL+H
Insert hyperlinkCTRL+K
New slideCTRL+M
Spell checkerF7
MacrosALT+F8

Run the slide show and press the F1 key to view all keyboard shortcuts applicable when running a slide show.

Tuesday 28 March 2017

MS Access ShortCuts

ACCESS

Action
Keystroke
Database actions
Open existing database
CTRL+O
Open a new database
CTRL+N
Save
CTRL+S
Save record
SHIFT+ENTER
Print
CTRL+P
Display database window
F11
Find and Replace
CTRL+F
Copy
CTRL+C
Cut
CTRL+X
Paste
CTRL+V
Undo
CTRL+Z
Help
F1
Toggle between Form and Design view
F5
Other
Insert line break in a memo field
CTRL+ENTER
Insert current date
CTRL+;
Insert current time
CTRL+:
Copy data from previous record
CTRL+'
Add a record
CTRL++
Delete a record
CTRL+-

Action
Keystroke
Editing
Select all
CTRL+A
Copy
CTRL+C
Cut
CTRL+X
Paste
CTRL+V
Undo
CTRL+Z
Redo
CTRL+Y
Find
CTRL+F
Replace
CTRL+H
Spell checker
F7
Toggle between Edit mode and Navigation mode
F2
Open window for editing large content fields
SHIFT+F2
Switch from current field to current record
ESC
Navigating Through a datasheet
Next field
TAB
Previous field
SHIFT+TAB
First field of record
HOME
Last field of record
END
Next record
DOWN ARROW
Previous record
UP ARROW
First field of first record
CTRL+HOME
Last field of last record
CTRL+END


Monday 13 February 2017

Find the total number of sheets in an Excel Workbook


VBA Code to Get the count of all sheets in an Excel Workbook

Sometime I come across with Excel reports with numerous worksheets / tabs.

Sometimes you can't find the starting or ending of the workbook.

Whenever I see those kind of Excel reports, I always wonder how many sheets will be there in that workbook !

Of Course, You can count the Excel sheets one by one. Select one sheet, then press CTRL + Tab to goto next sheet, keep on counting in your head. :) Very good time pass.

Excel is not providing a way to find how many worksheets are existing in a workbook.

If you want to know the count of all sheets in an Excel workbook, we have an easy way.

Steps:
  1. Goto VBE, by pressing ALT + F11 or Click on VisualBasic Icon from the Develepor Tab.
  2. Activate Immediate window by Pressing CTRL + G
  3. ?activeworkbook.Sheets.Count   Copy paste the highlighted VBA code to immediate window and press Enter
  4. You can see the count of the sheets in the active Excel workbook has printed just below the code which you have entered.

    msgbox activeworkbook.Sheets.Count If you use this code, a message box will be shown with count of sheets in the workbook.

This code becomes very important when you write VBA routines and loops.