Skip Clipboard during VBA.. operation

Recently i came across an error in Excel 2007 as

"Picture is too large and will be truncated."

Upon looking into the issue, i found that leaving stuff on the clipboard, used during the copy and paste operations within VBA was the culprit. This error occurred when the file was about to be closed by the user.
                                After digging in to many portals and unsuccessful attempts to resolve the error by clearing the clipboard via VBA, I found one way to fix the code by redesigning my VBA codes to skip the use of copy and paste and stick with the range based value transfers. Which later i realised is more robust and better way of doing things.

The simplest of the solutions to completed the copy and paste task is

DestinationRange.Value = SourceRange.Value
'with the format code later.

And the solution for the elimination of formulas and paste as values could be gained by

SelectionRange.Formula = SelectionRange.Value

As the office clipboard is now provided in task panes section its quite unclear as of now which solutions can clear the clipboard in VBA.

And to add upon the article

Application.CutCopyMode = False 

doesnt work to clear the office clipboard.

Let outlook do the thinking: Time based header for outlook

              Myself, being lazy tend to respect the people with greeting appropriate to the time of the day, like Good Morning, Good Afternoon & Good Evening.

   But keeping track of code, time, life and every thing else takes a toll and some times i try to automate these bits as much as possbile to simplify my life.
                  To cut short i desgined a macro which could populate the outlook text for new email with correct headers based on the time of day with the shortcut "Ctrl+J"

To accomplish this

1. Start you outlook..
2. Open a new email interface, press Alt+F11 on you key board to power up your VBA editor.

Navigate to location "Normal" as shown in the image below:



Insert a module named "CustomTextMacro" (any thing you like as it personal choice) 
In that module Type(if you like)/CopyPaste in the following peice of code:

Sub InsertCustomText_Mail()
Selection.HomeKey Unit:=wdStory
Selection.Font.Color = wdColorDarkBlue
If TimeValue(Time()) <= TimeValue("12:00:00") Then
Selection.TypeText Text:="Good Morning,"
ElseIf TimeValue(Time()) <= TimeValue("16:00:00") Then
Selection.TypeText Text:="Good Afternoon,"
ElseIf TimeValue(Time()) > TimeValue("16:00:00") Then
Selection.TypeText Text:="Good Evening,"
End If

Selection.TypeParagraph
Selection.TypeParagraph
Selection.TypeParagraph
Selection.TypeParagraph
Selection.TypeParagraph
Selection.TypeText Text:="Thank you"
Selection.TypeParagraph
Selection.TypeText Text:="Regards"
Selection.TypeParagraph
Selection.TypeText Text:="Amol Pandey"
Selection.TypeParagraph
Selection.TypeParagraph
End Sub

3. Then once completed, navigate to following location as shown in the image:



clicking on it will bring the following window:




then navigate to the Keyboard section of the window and click on it to bring on the following next window:




In this window we will find our macro listed and assigning the macro the required shortcut keys will accomplish the job and, there we have out complete automated solution for the custom outlook header.

Just press the magical shortcut, and there you go on your new email you have the custom header based correctly on the times of the day...

Hope it simplifies your lives as mine...

Cubed Formulas: Compute Data Throughout Workbook

           Recently i landed upon a brilliant techniques demonstrated by Better Solutions for illustrating the CUBED FORMULA, power of excel. This techniques thrust's excel to aggreagte same worksheet locations on diffrent sheets mentioned in the formula as workshet series.

 "Sheet1:Sheet4!A2:B5". This technique can be used in formula as   "=Sum(Sheet1:Sheet4!A2:B5)"

This technique would help aggreagate the certain ranges from each sheet specified adress and contained withing the worksheet series. Though being an advantage one has to be care ful to be aware that movement of order of sheets would alter the logic of formula performed and the intended result.

   For further information please check the refrence for more in detail description of the functionality.

Refrences:
Better Solutions : Link

Exploring Drop Down...Multicolumns

Many times the case where by one list of drop downs is not enough business request for multiple columns for the drop downs list for one chosen item.

Face with the issue heres my quick code for the same to overcome...

'Lets populate the and set the control parameters.
    obj.Clear 'Clear ne thing from the pre population of control if already done.
    obj.ColumnCount = 2 'Set the number of columns required to display.
    obj.ColumnWidths = "150;150" 'Set the size if columns
       
       For i = 1 To NoOfItems
                With obj
                    .AddItem "column1 item"
                    .List(custom_Counter, 1) = "column2 item"
                    custom_Counter = custom_Counter + 1
                End With
       Next i                         

'DropDown Change Event
      obj.List(obj.ListIndex , 0) 'Return you the selected 1st column value
      obj.List(obj.ListIndex , 1) 'Return you the selected 2nd column value

and there you go .. Thats out multi column drop down box....


Download solution