Archive for Formatting

Archives

  1. ActiveX Programming
  2. Add an Image to Cell Comments
  3. Add Sheet Navigation CommandBar to an Excel file or Application
  4. Auto-Generate a File Name
  5. Automate File Download from Internet
  6. Automate WebLogin…
  7. Convert Decimal to RGB Color Value
  8. Copy Ranges from Different Sheets into one Sheet
  9. Create HyperlinkObject on Sheet
  10. Create LogFile for Workbook Open
  11. Create Sheet “Table of Contents”
  12. Create ShortCut Link at the desired location
  13. Create UserForm at Runtime using Code
  14. Display Userform with no Title Bar (Splash Screen)
  15. Downloads
  16. Downloads from www.jkp-ads.com
  17. Export a Text File with Comma and Quote Delimiters
  18. Get elements from HTML form
  19. Get FileName with Path from User
  20. Get FileNames with Path from User
  21. Hyperlink Elements in HTML
  22. Ken’s Knowledge Base submissions
  23. Login to Rediffmail
  24. Move a File
  25. Multiple Cell Concatenation
  26. Other Site Links
  27. Read CSV Using VBA techniques
  28. Remove Unprintable Character & Spaces from cells
  29. Rename a File
  30. Rename Files using Codes (VBA)
  31. Resources
  32. Resources
  33. Search Folders and Subfolders for files
  34. Show Status on Userform While executing Code
  35. Site Contents
  36. socko.wordpress.com — Technorati Search
  37. Sort WorkSheets by Name
  38. Sorting Text with Worksheet Formula
  39. test
  40. Use of vbScript in Database Applications
  41. Userform Resizer – VBA
  42. Userforms
  43. VBA Code Library
  44. vbaX KB Links
  45. Verify that a File Exists
  46. Web Forms manipulated by Visual Basic
  47. Write to & Read from Sequential Text Files
Advertisements

Leave a Comment

Convert Decimal to RGB Color Value

– Selva V Pasupathy, HSBC Global Resourcing, Hyderabad
Last week , somebody sent me a mail asking if there is a way to convert decimal values of colors to rgb values. I thought excel solver addin already provides different ways to convert binary, decimal, hex values, so the function to convert decimal values to rgb values should be available in solver. But then I was not able to get function to convert decimal to rgb. I tried some functions in excel and made the function to convert decimal to rgb. I am not too sure if this is absolutely correct; but I have tried this with some of the decimals and I am getting the correct rgb value.

HOW SHOULD I USE THE FOLLOWING CODE

Let me know if you have any comment or feedback on this.

Private Function Convert_Dec2RGB(ByVal myDECIMAL As Long) As String
  Dim myRED As Long
  Dim myGREEN As Long
  Dim myBLUE As Long

  myRED = myDECIMAL And &HFF
  myGREEN = (myDECIMAL And &HFF00&) \ 256
  myBLUE = myDECIMAL \ 65536

  Convert_Dec2RGB = CStr(myRED) & "," & CStr(myGREEN) & "," & CStr(myBLUE)
End Sub

Leave a Comment

Reduce Excel File Size

-Selva V Pasupathy, HSBC Global Resourcing, Hyderabad1

Option Explicit
'       Written by Selva V Pasupathy
'       You are free to use the code and make changes
'       contact me at selvavinaygam (at) GMail (dot) com
Dim j               As Long
Dim k               As Long
Dim lRow         As Long
Dim lCol                  As Long
Dim cFormula      As Range
Dim rFormula      As Range
Dim cValue        As Range
Dim rValue        As Range
Dim myShape             As Shape
Dim mySheet              As Worksheet

Sub ExcelDiet()
On Error Resume Next
Application.ScreenUpdating = False
Application.DisplayAlerts = False
 For Each mySheet In Worksheets
   With mySheet
    On Error Resume Next
    Set cFormula = .Cells.Find(What:="*", _
            After:=Range("A1"), LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByColumns, _
            SearchDirection:=xlPrevious)
    Set cValue = .Cells.Find(What:="*", _
            After:=Range("A1"), LookIn:=xlValues, _
            LookAt:=xlPart, SearchOrder:=xlByColumns, _
            SearchDirection:=xlPrevious)
    Set rFormula = .Cells.Find(What:="*", _
            After:=Range("A1"), LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRomySheet, _
            SearchDirection:=xlPrevious)
    Set rValue = .Cells.Find(What:="*", _
            After:=Range("A1"), LookIn:=xlValues, _
            LookAt:=xlPart, SearchOrder:=xlByRomySheet, _
            SearchDirection:=xlPrevious)
    On Error GoTo 0
    If cFormula Is Nothing Then
        lCol = 0
    Else
        lCol = cFormula.Column
    End If
    If Not cValue Is Nothing Then
        lCol = Application.WorksheetFunction. _
                Max(lCol, cValue.Column)
    End If
    If rFormula Is Nothing Then
        lRow = 0
    Else
        lRow = rFormula.Row
    End If
    If Not rValue Is Nothing Then
        lRow = Application.WorksheetFunction. _
                Max(lRow, rValue.Row)
    End If
    For Each myShape In .Shapes
        j = 0
        k = 0
        On Error Resume Next
        j = myShape.TopLeftCell.Row
        k = myShape.TopLeftCell.Column
        On Error GoTo 0
        If j > 0 And k > 0 Then
            Do Until .Cells(j, k).Top > _
                    myShape.Top + myShape.Height
                j = j + 1
            Loop
            If j > lRow Then
                lRow = j
            End If
            Do Until .Cells(j, k).Left > _
                    myShape.Left + myShape.Width
                k = k + 1
            Loop
            If k > lCol Then
                lCol = k
            End If
        End If
    Next
    .Range(Cells(1, lCol + 1).Address & ":IV65536").Delete
    .Range(Cells(lRow + 1, 1).Address & ":IV65536").Delete
   End With
 Next
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

Leave a Comment

Remove Unprintable Character & Spaces from cells

There are times when you get text that has unprintable characters in them or extra spaces, typically from downloaded data or perhaps extracted data. This routine cleans your selected cells of thes characters.
-Selva V Pasupathy, HSBC Global Resources, Hyderabad.

Original Source: Ivan F Moala’s Site http://www.xcelfiles.com/VBA_Quick10.html
Option Explicit

Sub Clean_Trim()
‘// From Help Files:
‘// CLEAN > Removes all nonprintable characters from text.
‘// Use CLEAN on text imported from other applications that
‘// contains characters that may not print with your
‘// operating system.
‘// For example, you can use CLEAN to remove some low-level
‘// computer code that is frequently at the beginning and end
‘// of data files and cannot be printed.

‘// TRIM > Removes all spaces from text except for single
‘// spaces between words. Use TRIM on text that you have
‘// received from another application that may
‘// have irregular spacing.

Dim CleanTrimRg As Range
Dim oCell As Range
Dim Func As WorksheetFunction

Set Func = Application.WorksheetFunction

On Error Resume Next
Set CleanTrimRg = Selection.SpecialCells(xlCellTypeConstants, 2)
If Err Then MsgBox “No data to clean and Trim!”: Exit Sub

For Each oCell In CleanTrimRg
oCell = Func.Clean(Func.Trim(oCell))
Next

End Sub

Leave a Comment

Add an Image to Cell Comments

Add a bit of spice to your comments look by adding an Image file.
See results below.
Supported image file formats = .bmp; .gif; .tif; .jpg
-Selva V Pasupathy, HSBC Global Resourcing, Hyderabad
(Original Source: by Ivan Moala’s Site: http://www.xcelfiles.com/ )

Option Explicit

Const ImgFileFormat = "Image Files (*.bmp;*.gif;*.tif;*.jpg;*.jpeg)," & _
    "*bmp;*gif;*.tif;*.jpg;*.jpeg"

Sub AddPicturesToComments()
Dim HasCom
Dim Pict As String
Dim Ans As Integer

Set HasCom = ActiveCell.Comment
If Not HasCom Is Nothing Then ActiveCell.Comment.Delete
Set HasCom = Nothing

GetPict:
Pict = Application.GetOpenFilename(ImgFileFormat)
'Note you can load in, almost any file format
If Pict = "False" Then End

Ans = MsgBox("Open : " & Pict, vbYesNo + vbExclamation, "Use this Picture?")
If Ans = vbNo Then GoTo GetPict

With ActiveCell
    .AddComment
    .Comment.Visible = False
    .Comment.Shape.Fill.Transparency = 0#
    .Comment.Shape.Fill.UserPicture Pict
End With

End Sub

Leave a Comment

Camera Function in VBA

The following macro asks you to give a range to be copied, it then copies it as a picture, and prompts user to specify the area to paste it. The lasta part of the macro makes the “photo” dynamic, as if it is done manually with the camera tool. The PasteSpecial pastes the picture, and the picture remains selected. Setting the Formula property for the selection (the picture) results in getting the “photo” dynamic.
-Selva V Pasupathy, HSBC Global Resourcing, Hyderabad.


Option Explicit
Private i, j, k As Integer
Private strMyRange As String
Private sTitle As String
Private myRange As Range
Private NwRange As Range

Sub DoCamera()
Application.ScreenUpdating = True
strMyRange = “Select the range you would like to capture.”
sTitle = “User Input Required”
On Error Resume Next

Set myRange = Application.InputBox(Prompt:=strMyRange, _
Title:=sTitle, Default:=ActiveCell.Address, Type:=8)
If myRange Is Nothing Then End

On Error GoTo 0
myRange.CopyPicture
strMyRange = “Select the range on which you would like to paste.”
sTitle = “User Input Required”
On Error Resume Next
Set NwRange = Application.InputBox(Prompt:=strMyRange, _
Title:=sTitle, Default:=ActiveCell.Address, Type:=8)

If NwRange Is Nothing Then End
On Error GoTo 0
NwRange.PasteSpecial
Selection.Formula = myRange.Address
End Sub

Leave a Comment