Restrict entry into Textbox Control

November 26, 2008 at 14:40 · Filed under VBA Examples

- Selva V Pasupathy

When you are working with a userform to create a userinterface for data input, sometimes you would like to restrict the users to input numbers only in some of the textbox controls. However, many a times you would find accidently or otherwise, you find text entered into the database. You can use the following code to make sure that anything typed in textbox1 other than numbers 0 – 9 will not be accepted. Thus you can successfully implement to reduce errors in your database.
Private Sub TextBox1_KeyPress( _
      ByVal KeyAscii As MSForms.ReturnInteger)
  ‘ This code restricts any text in textbox1 other than
  ‘ integer 0 – 9 ….
  Select Case KeyAscii
    Case Asc(“0″) To Asc(“9″)
    
    Case Else
      KeyAscii = 0
  End Select
End Sub

Leave a Comment

Consolidating Data from >20 workbooks, each workbook Containing >70 worksheets

November 17, 2008 at 01:15 · Filed under Automation, VBA, VBA Code Library, VBA Examples

- Selva V Pasupathy, HSBC Global Resourcing, Hyderabad

Option Explicit
‘___________________________________________________________________________
‘***************************************************************************
‘*
‘* MODULE NAME:     Copy Data from all Excel Files in a Folder
‘* AUTHOR:          Selva V Pasupathy, HSBC Global Resourcing, Hyderabad
‘*
‘* CONTACT:         socko@rediffmail.com
‘* WEB SITE:        http://socko.wordpress.com
‘*
‘* DESCRIPTION:     The following visual basic code was written when
‘*                  I wanted to consolidate the data from 26 workbooks
‘*                  and in each workbook, there were are 77 sheets. It
‘*                  would have been a very difficult task, if I had to
‘*                  do it manually. I believe this procedure will be
‘*                  useful to somebody else
‘* NOTES:
‘*  Before using the codes, you can change all the variables like,
‘*  Folder, File , SearchString, SearchSubfolders (true / false),
‘*  and other variables.
‘*————————————————————————
‘* Other Information
‘*
‘* UPDATES:
‘*  DATE            COMMENTS
‘*  16 Nov 2008     You are free to use , change, and modify this code.
‘___________________________________________________________________________
‘***************************************************************************

Function ListFiles(sCount As Integer, ByVal sFldr As String, _
                bFldr As Boolean, sFileName As String) As String
Dim fCnt As Integer

    With Application.FileSearch
        .NewSearch
        .LookIn = sFldr
        .SearchSubFolders = bFldr
        .Filename = sFileName             ‘”*consolidated*.xls”
        .FileType = msoFileTypeAllFiles
        
        If .Execute() > 0 Then
            
            If .FoundFiles.Count > sCount Then
              
              For fCnt = 1 To .FoundFiles.Count
                  
                  If fCnt = sCount Then
                    
                    ListFiles = .FoundFiles(fCnt)
                  
                  End If
              
              Next i
            
            Else
            
            ListFiles = “”
            
            End If
        
        Else
            
            MsgBox “There were no files found.”
        
        End If
    
    End With

End Function

Sub Consolidate_Data()
Dim cFile As Integer
Dim sht, rw, cl, avLastRow As Long
Dim myFileName, myFolder, sFileName As String
Dim myFile As Workbook
Dim sFldr As Boolean

myFolder = “J:\GB Project\CONSOLIDATED”
sFldr = False
sFileName = “*consolidated*.xls”

With ThisWorkbook.Sheets(“data”)
  For cFile = 13 To 100
    myFileName = ListFiles(cFile, myFolder, True, sFileName)
    If myFileName = “” Then Exit Sub
      Set myFile = Workbooks.Open(myFileName)
      ThisWorkbook.Activate
      ThisWorkbook.Sheets(“data”).Activate
        For sht = 2 To myFile.Sheets.Count
          For rw = 1 To 100
            avLastRow = .Cells(65536, 1).End(xlUp).Row + 1
            ThisWorkbook.Sheets(“data”).Cells(avLastRow, 1).Activate
            .Cells(avLastRow, 1) = myFile.Sheets(sht).Name
            For cl = 1 To 18
            Application.StatusBar = “Writing File# ” & cFile & _
                    ”  Sheet# ” & sht
            .Cells(avLastRow, cl + 1) = myFile.Sheets(sht).Cells(rw, cl)
            Next cl
          Next rw
        Next sht
  myFile.Close False
  ThisWorkbook.Save
  Next cFile
End With
End Sub

Leave a Comment

Archives

November 16, 2008 at 20:22 · Filed under Automation, Charting, Downloads, Formatting, Tutorials, Userforms, VBA, VBA Advanced, VBA Code Library, VBA Examples

  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

Leave a Comment

Add Sheet Navigation control on ToolBar

November 9, 2008 at 14:45 · Filed under CommandBar, CommandBarCombobox, CommandBarControl, SheetNavigation, VBA Examples

- Selva V Pasupathy,   HSBC Global Resourcing, Hyderabad

Add the following code to a code module of excel workbook and run macro named “AddComboNavigation”. This will add a combobox control on standard toolbar on microsoft excel. This will make sure that you will be able to navigate between sheets, especially wehn you have a workbook with 100’s of sheets. This is one think that I would like to use and dont have problem even creating workbooks with 100s of worksheets.

Option Explicit
Sub AddComboNavigation()
  Dim cBar As CommandBar
  Dim c As CommandBarComboBox
  Dim i As Integer
  ‘ Set reference to standard toolbar
  Set cBar = Application.CommandBars(“standard”)
  cBar.Reset
  
  ‘Add Combobox Control
  Set c = cBar.Controls.Add(msoControlComboBox, 1)
  
  With c
    .Clear
    For i = 1 To ThisWorkbook.Sheets.Count
      .AddItem ThisWorkbook.Sheets(i).Name, 1
    Next i
      .Caption = “Sheet Navigator”
      .DescriptionText = “This is the area where you can place description area”
      .Enabled = True
      .Visible = True
      .DropDownLines = 5
      .ListIndex = 0
      .OnAction = “Activate_Sheet”
  End With
End Sub

Private Sub Activate_Sheet()
  ‘on error resume next
  Dim x As String
  Dim c As CommandBarComboBox
  
  Set c = Application.CommandBars(“standard”).Controls(“Sheet Navigator”)
  If c.ListIndex 0 Then
  Sheets(c.ListCount – c.ListIndex + 1).Activate
  End If
End Sub

Leave a Comment

Userform Resizer – VBA

October 25, 2008 at 11:38 · Filed under VBA Examples

- Selva V Pasupathy, HSBC Global Resourcing, Hyderabad 1

Copy following code and paste in a userform module to add form resizer.
Original Source: Andy Pope

Option Explicit
Private Const MResizer = "ResizeGrab"
Private WithEvents m_objResizer As MSForms.Label
Private m_sngLeftResizePos As Single
Private m_sngTopResizePos As Single
Private m_blnResizing As Single
'________________________________________
Private Sub UserForm_Initialize()
    m_AddResizer
End Sub
'________________________________________
Private Sub UserForm_Terminate()
    Me.Controls.Remove MResizer
End Sub
'________________________________________
Private Sub m_AddResizer()
'
'add resizing control to bottomright corner of form
'
Set m_objResizer = Me.Controls.Add _
    ("Forms.label.1", MResizer, True)
With m_objResizer
  With .Font
      .Name = "Marlett"
      .Charset = 2
      .Size = 14
      .Bold = True
  End With
  .BackStyle = fmBackStyleTransparent
  .AutoSize = True
  .BorderStyle = fmBorderStyleNone
  .Caption = "o"
  .MousePointer = fmMousePointerSizeNWSE
  .ForeColor = RGB(100, 100, 100)
  .ZOrder
  .Top = Me.InsideHeight - .Height
  .Left = Me.InsideWidth - .Width
End With
End Sub
'________________________________________
Private Sub m_objResizer_MouseDown( _
            ByVal Button As Integer, _
            ByVal Shift As Integer, _
            ByVal X As Single, _
            ByVal Y As Single)
    If Button = 1 Then
        m_sngLeftResizePos = X
        m_sngTopResizePos = Y
        m_blnResizing = True
    End If
End Sub
'________________________________________
Private Sub m_objResizer_MouseMove( _
            ByVal Button As Integer, _
            ByVal Shift As Integer, _
            ByVal X As Single, _
            ByVal Y As Single)
  If Button = 1 Then
    With m_objResizer
      .Move .Left + X _
            - m_sngLeftResizePos, _
            .Top + Y - m_sngTopResizePos
      Me.Width = Me.Width + _
            X - m_sngLeftResizePos
      Me.Height = Me.Height + Y - m_sngTopResizePos
      .Left = Me.InsideWidth - .Width
      .Top = Me.InsideHeight - .Height
    End With
  End If
End Sub
'________________________________________
Private Sub m_objResizer_MouseUp( _
            ByVal Button As Integer, _
            ByVal Shift As Integer, _
            ByVal X As Single, _
            ByVal Y As Single)
    If Button = 1 Then
        m_blnResizing = False
    End If
End Sub

Leave a Comment

Sorting Text with Worksheet Formula

October 23, 2008 at 21:30 · Filed under 12628570, Sorting, Worksheet Formula

- Selva V Pasupathy, HSBC Global Resourcing, Hyderabad1

I was trying to see if text sorting can be done with the help of worksheet formula. I tried and I think I am successful to some extent. Sorting text is such a day to day activity, it surprises me why Microsoft hasn’t provided a worksheet formula for doing it.

I know that you always had a choice of autofilter but it requires manual steps. While trying to wrok with countif() formula, I came across with following worksheet array formula which sorts the array. “myArray” is the name of the array this worksheet formula would sort. Please remember, you need to use it as an array formula, i.e., select the whole area where you want the output, and then type your formula and while entering please hold on to ctrl+shift and then press enter.

=INDEX(myarray,MATCH(SMALL(COUNTIF(myarray,”<=”&myarray),ROW(myarray)-ROW(INDEX(myarray,ROWS(myarray),0))+ROWS(myarray)),COUNTIF(myarray,”<=”&myarray),0),0)

Leave a Comment

Show Formula in a cell

October 23, 2008 at 20:15 · Filed under VBA Examples

- Selva V Pasupathy, HSBC Global Resourcing, Hyderabad1
Today someone asked me how to display a formula in a cell. What a silly question? It’s Easy, just enter an apostrophe (‘) in front of the formula and one can see the formula. Oh No! But if you don’t want to disturb the cell where the formula resides? Oh! So you want to show the result of the formula as well… Yes! at the same time a user should be able to see the formula as it is in a different cell. Isn’t it simple? Enter into the cell, copy the formula starting “=” and till the end and paste it in a different cell and add apostrophe in front of it. But what if, you have thousands of formula to show? Now, there is something to work on…. Let me share what i tried sometime back and i think this should work!

Open a new Excel Workbook, press alt+F11, if you dont see a small window with the names of the sheets in a workbook, click ctrl+R… Now you will be able to see a window with all the workbooks and its contents like sheets, userforms, & codemodules. Right click the new workbook project and choose insert –> module… Now double click the module that has just been inserted… Do you see code window? If you are not, press F7 key and you will see the code module. Now in this module, copy the following code and paste it. 


Option Explicit
Public Function ShowFormula(ByRef iCell As Range)
  If iCell.HasFormula = True Then
    ShowFormula = iCell.Formula
  Else
      ShowFormula = ""
  End If
End Function

Now, you are ready to show the user the formula as it is wherever you want. just type "=showformula(" and then the reference to a cell to display the formula of that particular cell.

eg., =showformula(e6)

To download an example workbook, CLICK HERE

Leave a Comment

« Newer Posts · Older Posts »