- ActiveX Programming
- Add an Image to Cell Comments
- Add Sheet Navigation CommandBar to an Excel file or Application
- Auto-Generate a File Name
- Automate File Download from Internet
- Automate WebLogin…
- Convert Decimal to RGB Color Value
- Copy Ranges from Different Sheets into one Sheet
- Create HyperlinkObject on Sheet
- Create LogFile for Workbook Open
- Create Sheet “Table of Contents”
- Create ShortCut Link at the desired location
- Create UserForm at Runtime using Code
- Display Userform with no Title Bar (Splash Screen)
- Downloads
- Downloads from www.jkp-ads.com
- Export a Text File with Comma and Quote Delimiters
- Get elements from HTML form
- Get FileName with Path from User
- Get FileNames with Path from User
- Hyperlink Elements in HTML
- Ken’s Knowledge Base submissions
- Login to Rediffmail
- Move a File
- Multiple Cell Concatenation
- Other Site Links
- Read CSV Using VBA techniques
- Remove Unprintable Character & Spaces from cells
- Rename a File
- Rename Files using Codes (VBA)
- Resources
- Resources
- Search Folders and Subfolders for files
- Show Status on Userform While executing Code
- Site Contents
- socko.wordpress.com — Technorati Search
- Sort WorkSheets by Name
- Sorting Text with Worksheet Formula
- test
- Use of vbScript in Database Applications
- Userform Resizer – VBA
- Userforms
- VBA Code Library
- vbaX KB Links
- Verify that a File Exists
- Web Forms manipulated by Visual Basic
- Write to & Read from Sequential Text Files
Archive for Formatting
Archives
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
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
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
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
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