Thursday, 30 October 2014

WorkSheet Event to Zooming in and Out of a Worksheet with Double Click

WorkSheet Event BeforeDoubleClick for Zooming in and Out of a Worksheet with Double Click.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

'Check Current Zoom state
'Zoom to 100% if Zoom Level< 100
'Zoom to 150% if Zoom Level= 100

    If ActiveWindow.Zoom <> 100 Then
    ActiveWindow.Zoom = 100
    Else
    ActiveWindow.Zoom = 150
    End If

End Sub

WorkSheet Evet Image View :



What this Event will do Exactly :

Suppose a sheet has a Zoom Level of 60% as follows:

If you double click on any cell of a Sheet , it will Zooming the Sheet to 100% when Zoom Level is Less or Greater than it.

Again If you double click on any cell of a Sheet , it will Zooming the Sheet to 150% when Zoom Level is at 100%.

Again If you double click on any cell of a Sheet , it will Zooming back the Sheet to 100% as the Zoom Level is at 150% which Greater than 100%.

Thanks
TAMATAM


Workbook Event BeforeSave Syntax and Example

Workbook Event BeforeSave to Prompts the User for his Response to really Save the Workbook or Not.
The following  example prompts the user for a YES or NO response before saving any workbook.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Alert = MsgBox("Press Yes to Save the Workbook", vbYesNo, "Do You Really Wants to Save ?")
If Alert = vbNo Then Cancel = True
    
End Sub


BeforeSave Event Image View :






Prompt for the User :
Each time when you press "Ctrl+S" , the Event will occur and Prompt the Msgbox for User Response as below:

Workbook Event BeforeClose to Prevent the Closing of a Workbook when Specific Cell Value is Blank

How to Prevent the Closing of a Workbook with WorkBook Event when Specific Cell Value is Blank 

We can do this by using the WorkBook Event "BeforeClose". Just use this Code in this Event Module.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

'Check to see if Cell C5 is blank
If Sheets("Sheet1").Range("B5").Value = "" Then
    
'If Blank: Cancel the Closing of WorkBook and Tell the User
    Cancel = True
    
    MsgBox "Pleas Fill Cell B5 with Value then Try to Close the Workbook", vbOKOnly, "Hi ! The Cell B5 Can Not be Blank"

'If Not Blank; Save and Close the Workbook
Else
    ActiveWorkbook.Close SaveChanges:=True
End If

End Sub

WorkBook Event Image View :



Explanation :
Suppose a Worksheet is there where the Range("B5").Value is Mandatory.
As B5 is Mandatory Cell which should not be Blank. Though if you try to Close the Workbook without filling that B5 Cell , then you get the following Message alert as per about Event.



Thanks,
TAMATAM

Wednesday, 29 October 2014

Work Sheet Change Event to Save a Workbook on Changing a Cell Value in a Specified Range

How to Save Workbook on Changing a Cell Value in a Specified Range
The following worksheet event triggers and saves the Workbook when you make change to the cells in any specified Range ( say , "C5:C10" ).
Private Sub Worksheet_Change(ByVal Target As Range)
''Check to see if the Changed Cell is exists with in Specified range
    If Intersect(Target, Range("C5:C10")) Is Nothing Then

    Exit Sub ' Exit if Not

    Else
    ActiveWorkbook.Save

    End If

End Sub


Example II :
The following WorkSheet Event is used to do the duplicate entry in Main sheet while you making entry in the Sub Sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
    
    On Error GoTo ErrorHandle:
    If Intersect(Target, Range("A:C")) Is Nothing Then

        Exit Sub ' Exit if the Entry is not happening in Specified Columns

    Else
    Rng = Target.Address 'The Target Cell Address
    RngVal = Range(Rng).Value
        If RngVal <> "" Then
            ThisWorkbook.Sheets("Main").Range(Rng).Value = RngVal
        End If
    End If
    
ErrorHandle:
    If Err.Number = 13 Then Resume Next

End Sub

How to Save or Export Active Sheet Data into a New Workbook

Excel VBA Macro to Save or Export Active Sheet Data into a New Workbook

Method-I:

Sub Save_ActSht2New_WB()
    ActiveSheet.Copy

    Application.DisplayAlerts = False

    ActiveWorkbook.SaveAs "C:\Users\Tamatam\Desktop\Temp\ActShtData.xlsx"
    ActiveWorkbook.Close True
    
    Application.DisplayAlerts = True
End Sub
----------------------------------------------------------------------------

Method-II:

Sub ExpActSht2New_WB()
'Step 1 Copy the Activesheet data
    ActiveSheet.Cells.Copy
    ActShtName = ActiveSheet.Name

'Step 2 Create a new workbook with Single sheet
    Workbooks.Add (xlWBATWorksheet)

'Step 3 Paste the data and change the sheet name to ActiveSheet Name
    ActiveSheet.Paste
    ActiveSheet.Range("A1").Select
    Application.CutCopyMode = False
    ActiveSheet.Name = ActShtName

'Step 4 Turn off application alerts
    Application.DisplayAlerts = False
    
'Step 5 Save the newly created workbook
    ActiveWorkbook.SaveAs "C:\Users\Tamatam\Desktop\Temp\ActShtData.xlsx"
    ActiveWorkbook.Close True

'Step 6 Turn application alerts back on
    Application.DisplayAlerts = True

End Sub


Sunday, 26 October 2014

How to Apply Conditional Formatting to a Pivot Table

Pivot Table Data Conditional Formatting
As we already know that Conditional Formatting is used to Format the Cells based on some Condition applied on Cell Values.
In the We apply Conditional Formatting to a Pivot Table as explained below:
This format will apply to the data section and it keep effecting even data decreases or decreases...........
First select any Cell in the Data section of the Pivot Table for which we want to apply Conditional Format.



Next go to Home > Conditional Formatting > New Rule



Next Select the "Apply Rule to " option , the Condition and Format of Cells as per below :
Since we are applying the Conditional Format to a Pivot Data , we have to Care Fully select the "Apply Rule To" options as shown below :

Method I :
 Apply Rules to " All Cell showing "Sum of Sales" values for "Sales_Period" and "Prod_ID" :

Since we selected the Apply Rules to " All Cell showing "Sum of Sales" values for "Sales_Period" and "Prod_ID" , the Format will apply to the "Sum of Sales" Data > 150 of the Two Column Labels "Sales_Period" and "Prod_ID" as shown below :
Result :



Method II :
"Apply Rule to " option only for Cells that contain "Sum of Sales" :
Suppose if we select "Apply Rule to " option only for Cells that contain "Sum of Sales" as shown below , the result will be effect on Grand Totals also as shown below :

Result :


Conclusion:
Method-I is appropriate for Conditional Formatting on Pivot Tables.


Thanks,
TAMATAM
BI-Analyst

Saturday, 25 October 2014

How to Create and Select Dynamic Range for a Pivot Table or Chart

Excel OFFSET Function to Create Dynamic Range for a Column or Range of Pivot Table or Chart
Here we will discuss about how to Define a Dynamic Range for a Pivot Table. The same Technique can we use for Charts and other things wherever dynamism is required.
Let us Suppose if we have a Pivot table like as Follows...




Source data as follows...




Define Dynamic Named Range with OFFSET Function :
We can use Offset and Count Combo formula to Define the Dynamic Range.
Suppose if your data only in one Column(Say Column A) , Then for Dynamic Range we can use the following formula:

 =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

But in our Example we have the Data in three Columns A,B,C,D.
So we can use the below formula to define Dynamic Named Range for our Pivot Table or Chart.

 =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),4)

Dynamic Named Range :
To define "Dynamic Named Range", first we have select the Data.
Next Define the Name for It from Formulas>Name Manager>Define New Name.
Next choose Refers to Range OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),4)
Next say "OK"


Now use Dynamic Named Range "Dyn_Data" as a Pivot Data Source as shown below :



Now Increase the Source and then refresh the Pivot and Check whether the Pivot is refers to the updated Dynamic data or not.

Yes , It will refer to the Dynamic data source as shown below :


Pivot after refresh as follows :



Note :
We may get an error if we try to use this Formula directly in Table/Rage address bar. Thats why we have to use them in the Named Ranges.

Thanks,
TAMATAM




Friday, 17 October 2014

How to Run an Access Macro from Excel VBA Macro

Excel VBA Macro to Run an Access Macro
Suppose we have a MS Access Database .In this Access DB File , we have a Macro called "Clear_Tables" , this Macro is designed in the MS Access file , which will run the some Queries internally designed in MS Access File, as shown below :


Now If  you want to Run the above Macro "Clear_Tables"  in above Access File , you can simply provide the Details of  the Access File , Macro Name in the Excel Sheet from where you are running that Macro, as shown below :



Finally Run the following Macro from Excel , which will Run an Access Macro 

Sub Clean_DB_Files()

Dim SrcDB_Name As String
Dim SrcFolderPath As String
Dim SrcFilExt As String
Dim SourceDB_File 'As String
Dim TargetFolderPath As String
Dim WS As Worksheet
Dim Acc_DB As Object
Dim FSO As Object

Set WS = ThisWorkbook.Sheets("Clean_DB_Files")
Set Acc_DB = CreateObject("Access.Application")
Set FSO = CreateObject("Scripting.FileSystemObject")

For X = 2 To 100
If WS.Cells(X, 2) = "" Then Exit For

SrcDB_Name = WS.Cells(X, 1)
SrcFilExt = WS.Cells(X, 2)
DB_Macro = WS.Cells(X, 3)
SrcFolderPath = WS.Cells(X, 4)


    If Right(SrcFolderPath, 1) <> "\" Then
        SrcFolderPath = SrcFolderPath & "\"
    
    End If

'Checking the Folder Path existence

If FSO.FolderExists(SrcFolderPath) = False Then
WS.Cells(X, 5) = "Failed"
MsgBox ("Source Folder Does Not Exist or Path Not Found")
GoTo Nxt:
End If

SourceDB_File = SrcFolderPath & SrcDB_Name & SrcFilExt

'Checking the Source DB File existence in the Path
If FSO.FileExists(SourceDB_File) = False Then
WS.Cells(X, 5) = "Failed"
MsgBox ("Source DB File Does Not Exist or Path Not Found")
GoTo Nxt:
End If

Acc_DB.Visible = False
Acc_DB.OpenCurrentDatabase (SourceDB_File)
Acc_DB.DoCmd.RunMacro DB_Macro
Acc_DB.CloseCurrentDatabase

WS.Cells(X, 5) = "Success"

Nxt:
Next X

End Sub

Note :
In the Excel File shown above you can list out all the MS-Access files , and corresponding existed Macros of each Access file , from which you want to Run the Macros.

Make sure that the Macro which you want to execute should exist in the Source Database File.

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts