Archive for October, 2008

Get FileNames with Path from User

October 9, 2008 at 11:33 · Filed under VBA Examples

HOW SHOULD I USE THE FOLLOWING CODE

Option Explicit

Dim outfile       As String
Dim Num           As Integer
Dim dlg           As FileDialog
Dim nFile         As Integer

Sub Get_FileNames()
OneMoreTime:
  nFile = FreeFile
  If ThisWorkbook.Path = "" Then
    MsgBox "PLEASE SAVE THIS WORKBOOK BEFORE EXECUTING THIS PROCEDURE"
    Exit Sub
  End If

  outfile = ThisWorkbook.Path & "\" & "Files_" & Format(Now(), "hh_nn_ss") & ".txt"
  Open outfile For Output As #nFile
  Set dlg = Application.FileDialog(msoFileDialogFilePicker)

  Dim selItem As Variant
  With dlg
    If .Show = -1 Then
      For Each selItem In .SelectedItems
        Write #nFile, selItem
      Next selItem
    Else
    End If
  End With

  Close #nFile

  Set dlg = Nothing
  Dim result
  result = MsgBox("A textfile with selected " & _
        "file name with path is saved at : " & _
          vbNewLine & outfile & vbNewLine & _
          "Would you like to save some more file names " & _
          "in a text file", vbOKCancel + vbQuestion, "Selva V Pasupathy")
  If result = vbOK Then
    GoTo OneMoreTime
  End If
End Sub

Leave a Comment

Convert Decimal to RGB Color Value

October 7, 2008 at 16:28 · Filed under Formatting, VBA, VBA Examples, VBA RGB COLOR

- 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

Clear Controls on Userform

October 6, 2008 at 19:14 · Filed under Automation, Userforms, VBA, VBA Examples

- Selva V Pasupathy, HSBC Global Resourcing, Hyderabad
The following code can be used to clear all textboxes and comboboxes on userform.

HOW SHOULD I USE THE FOLLOWING CODE

Private Sub Clear_all()
    For I = 0 To UserForm1.Controls.Count - 1

        If UCase(TypeName(UserForm1.Controls(I))) = "TEXTBOX" Then
            UserForm1.Controls(I).Value = ""
        End If
        If UCase(TypeName(UserForm1.Controls(I))) = "COMBOBOX" Then
            UserForm1.Controls(I).Value = ""
        End If
    Next I
End Sub

Leave a Comment

« Newer Posts