Archive for December, 2008

Add Control to CommandBars in WorksheetMenuBar

December 21, 2008 at 23:41 · Filed under Code Snippets, CommandBarControl ·Tagged

Sub AddControlToWorksheetMenuBar(ByRef NewCmdControl As String, _
              ByRef cmdBar As String, ByRef Face_ID As Long, _
              ByRef On_Action_Proc As String, _
              Optional ByRef Begin_Group As Boolean = False)
  ‘   Created by Selva V Pasupathy, Hyderabad
  ‘ This custom procedure adds a button control to commandbar
  ‘ Can be used to add a control to one of the worksheet menu bar
  ‘ where NewCmdControl = Caption for new CommandBarControl
  ‘ & NewCmdControl will be the name to that control
  ‘ Face_ID = one of the long integers for face_id
  
  Dim newItem As CommandBarControl

  With CommandBars(cmdBar)
    
    With .Controls(NewCmdControl)
      On Error Resume Next
      .Delete
      On Error GoTo 0
    End With
    
    Set newItem = .Controls.Add(Type:=msoControlButton)
    
    With newItem
      .BeginGroup = Begin_Group
      .Caption = NewCmdControl
      .FaceId = Face_ID
      .OnAction = “qtrReport”
    End With
  End With

End Sub

Leave a Comment

Use BuiltIn Dialogs in Excel

December 20, 2008 at 20:55 · Filed under VBA Examples

Selva V Pasupathy, HSBC Global Resourcing, Hyderabad

Rather than designing a userform for a routine tasks to be performed by a user on excel, we can use 200+ builtin dialogs provided by excel. The code would be as follows

Application.Dialogs(xlDialogSaveAs).Show

A tabel with various xlbuiltindialogs, constant and description is given below:

Name Constant Value Description
xlDialogActivate 103 Activate dialog box
xlDialogActiveCellFont 476 Active Cell Font dialog box
xlDialogAddChartAutoformat 390 Add Chart Autoformat dialog box
xlDialogAddinManager 321 Addin Manager dialog box
xlDialogAlignment 43 Alignment dialog box
xlDialogApplyNames 133 Apply Names dialog box
xlDialogApplyStyle 212 Apply Style dialog box
xlDialogAppMove 170 AppMove dialog box
xlDialogAppSize 171 AppSize dialog box
xlDialogArrangeAll 12 Arrange All dialog box
xlDialogAssignToObject 213 Assign To Object dialog box
xlDialogAssignToTool 293 Assign To Tool dialog box
xlDialogAttachText 80 Attach Text dialog box
xlDialogAttachToolbars 323 Attach Toolbars dialog box
xlDialogAutoCorrect 485 Auto Correct dialog box
xlDialogAxes 78 Axes dialog box
xlDialogBorder 45 Border dialog box
xlDialogCalculation 32 Calculation dialog box
xlDialogCellProtection 46 Cell Protection dialog box
xlDialogChangeLink 166 Change Link dialog box
xlDialogChartAddData 392 Chart Add Data dialog box
xlDialogChartLocation 527 Chart Location dialog box
xlDialogChartOptionsDataLabelMultiple 724 Chart Options DataLabel Multiple dialog box
xlDialogChartOptionsDataLabels 505 Chart Options DataLabels dialog box
xlDialogChartOptionsDataTable 506 Chart Options DataTable dialog box
xlDialogChartSourceData 540 Chart SourceData dialog box
xlDialogChartTrend 350 Chart Trend dialog box
xlDialogChartType 526 Chart Type dialog box
xlDialogChartWizard 288 ChartWizard dialog box
xlDialogCheckboxProperties 435 Checkbox Properties dialog box
xlDialogClear 52 Clear dialog box
xlDialogColorPalette 161 Color Palette dialog box
xlDialogColumnWidth 47 Column Width dialog box
xlDialogCombination 73 Combination dialog box
xlDialogConditionalFormatting 583 Conditional Formatting dialog box
xlDialogConsolidate 191 Consolidate dialog box
xlDialogCopyChart 147 Copy Chart dialog box
xlDialogCopyPicture 108 Copy Picture dialog box
xlDialogCreateList 796 Create List dialog box
xlDialogCreateNames 62 Create Names dialog box
xlDialogCreatePublisher 217 Create Publisher dialog box
xlDialogCustomizeToolbar 276 Customize Toolbar dialog box
xlDialogCustomViews 493 Custom Views dialog box
xlDialogDataDelete 36 Data Delete dialog box
xlDialogDataLabel 379 Data Label dialog box
xlDialogDataLabelMultiple 723 Data Label Multiple dialog box
xlDialogDataSeries 40 Data Series dialog box
xlDialogDataValidation 525 Data Validation dialog box
xlDialogDefineName 61 Define Name dialog box
xlDialogDefineStyle 229 Define Style dialog box
xlDialogDeleteFormat 111 Delete Format dialog box
xlDialogDeleteName 110 Delete Name dialog box
xlDialogDemote 203 Demote dialog box
xlDialogDisplay 27 Display dialog box
xlDialogDocumentInspector 862 Document Inspector dialog box
xlDialogEditboxProperties 438 Editbox Properties dialog box
xlDialogEditColor 223 Edit Color dialog box
xlDialogEditDelete 54 Edit Delete dialog box
xlDialogEditionOptions 251 Edition Options dialog box
xlDialogEditSeries 228 Edit Series dialog box
xlDialogErrorbarX 463 Errorbar X dialog box
xlDialogErrorbarY 464 Errorbar Y dialog box
xlDialogErrorChecking 732 Error Checking dialog box
xlDialogEvaluateFormula 709 Evaluate Formula dialog box
xlDialogExternalDataProperties 530 External Data Properties dialog box
xlDialogExtract 35 Extract dialog box
xlDialogFileDelete 6 File Delete dialog box
xlDialogFileSharing 481 File Sharing dialog box
xlDialogFillGroup 200 Fill Group dialog box
xlDialogFillWorkgroup 301 Fill Workgroup dialog box
xlDialogFilter 447 Dialog Filter dialog box
xlDialogFilterAdvanced 370 Filter Advanced dialog box
xlDialogFindFile 475 Find File dialog box
xlDialogFont 26 Font dialog box
xlDialogFontProperties 381 Font Properties dialog box
xlDialogFormatAuto 269 Format Auto dialog box
xlDialogFormatChart 465 Format Chart dialog box
xlDialogFormatCharttype 423 Format Charttype dialog box
xlDialogFormatFont 150 Format Font dialog box
xlDialogFormatLegend 88 Format Legend dialog box
xlDialogFormatMain 225 Format Main dialog box
xlDialogFormatMove 128 Format Move dialog box
xlDialogFormatNumber 42 Format Number dialog box
xlDialogFormatOverlay 226 Format Overlay dialog box
xlDialogFormatSize 129 Format Size dialog box
xlDialogFormatText 89 Format Text dialog box
xlDialogFormulaFind 64 Formula Find dialog box
xlDialogFormulaGoto 63 Formula Goto dialog box
xlDialogFormulaReplace 130 Formula Replace dialog box
xlDialogFunctionWizard 450 Function Wizard dialog box
xlDialogGallery3dArea 193 Gallery 3D Area dialog box
xlDialogGallery3dBar 272 Gallery 3D Bar dialog box
xlDialogGallery3dColumn 194 Gallery 3D Column dialog box
xlDialogGallery3dLine 195 Gallery 3D Line dialog box
xlDialogGallery3dPie 196 Gallery 3D Pie dialog box
xlDialogGallery3dSurface 273 Gallery 3D Surface dialog box
xlDialogGalleryArea 67 Gallery Area dialog box
xlDialogGalleryBar 68 Gallery Bar dialog box
xlDialogGalleryColumn 69 Gallery Column dialog box
xlDialogGalleryCustom 388 Gallery Custom dialog box
xlDialogGalleryDoughnut 344 Gallery Doughnut dialog box
xlDialogGalleryLine 70 Gallery Line dialog box
xlDialogGalleryPie 71 Gallery Pie dialog box
xlDialogGalleryRadar 249 Gallery Radar dialog box
xlDialogGalleryScatter 72 Gallery Scatter dialog box
xlDialogGoalSeek 198 Goal Seek dialog box
xlDialogGridlines 76 Gridlines dialog box
xlDialogImportTextFile 666 Import Text File dialog box
xlDialogInsert 55 Insert dialog box
xlDialogInsertHyperlink 596 Insert Hyperlink dialog box
xlDialogInsertObject 259 Insert Object dialog box
xlDialogInsertPicture 342 Insert Picture dialog box
xlDialogInsertTitle 380 Insert Title dialog box
xlDialogLabelProperties 436 Label Properties dialog box
xlDialogListboxProperties 437 Listbox Properties dialog box
xlDialogMacroOptions 382 Macro Options dialog box
xlDialogMailEditMailer 470 Mail Edit Mailer dialog box
xlDialogMailLogon 339 Mail Logon dialog box
xlDialogMailNextLetter 378 Mail Next Letter dialog box
xlDialogMainChart 85 Main Chart dialog box
xlDialogMainChartType 185 Main Chart Type dialog box
xlDialogMenuEditor 322 Menu Editor dialog box
xlDialogMove 262 Move dialog box
xlDialogMyPermission 834 My Permission dialog box
xlDialogNameManager 977 NameManager dialog box
xlDialogNew 119 New dialog box
xlDialogNewName 978 NewName dialog box
xlDialogNewWebQuery 667 New Web Query dialog box
xlDialogNote 154 Note dialog box
xlDialogObjectProperties 207 Object Properties dialog box
xlDialogObjectProtection 214 Object Protection dialog box
xlDialogOpen 1 Open dialog box
xlDialogOpenLinks 2 Open Links dialog box
xlDialogOpenMail 188 Open Mail dialog box
xlDialogOpenText 441 Open Text dialog box
xlDialogOptionsCalculation 318 Options Calculation dialog box
xlDialogOptionsChart 325 Options Chart dialog box
xlDialogOptionsEdit 319 Options Edit dialog box
xlDialogOptionsGeneral 356 Options General dialog box
xlDialogOptionsListsAdd 458 Options Lists Add dialog box
xlDialogOptionsME 647 OptionsME dialog box
xlDialogOptionsTransition 355 Options Transition dialog box
xlDialogOptionsView 320 Options View dialog box
xlDialogOutline 142 Outline dialog box
xlDialogOverlay 86 Overlay dialog box
xlDialogOverlayChartType 186 Overlay ChartType dialog box
xlDialogPageSetup 7 Page Setup dialog box
xlDialogParse 91 Parse dialog box
xlDialogPasteNames 58 Paste Names dialog box
xlDialogPasteSpecial 53 Paste Special dialog box
xlDialogPatterns 84 Patterns dialog box
xlDialogPermission 832 Permission dialog box
xlDialogPhonetic 656 Phonetic dialog box
xlDialogPivotCalculatedField 570 Pivot Calculated Field dialog box
xlDialogPivotCalculatedItem 572 Pivot Calculated Item dialog box
xlDialogPivotClientServerSet 689 Pivot Client Server Set dialog box
xlDialogPivotFieldGroup 433 Pivot Field Group dialog box
xlDialogPivotFieldProperties 313 Pivot Field Properties dialog box
xlDialogPivotFieldUngroup 434 Pivot Field Ungroup dialog box
xlDialogPivotShowPages 421 Pivot Show Pages dialog box
xlDialogPivotSolveOrder 568 Pivot Solve Order dialog box
xlDialogPivotTableOptions 567 Pivot Table Options dialog box
xlDialogPivotTableWizard 312 Pivot Table Wizard dialog box
xlDialogPlacement 300 Placement dialog box
xlDialogPrint 8 Print dialog box
xlDialogPrinterSetup 9 Printer Setup dialog box
xlDialogPrintPreview 222 Print Preview dialog box
xlDialogPromote 202 Promote dialog box
xlDialogProperties 474 Properties dialog box
xlDialogPropertyFields 754 Property Fields dialog box
xlDialogProtectDocument 28 Protect Document dialog box
xlDialogProtectSharing 620 Protect Sharing dialog box
xlDialogPublishAsWebPage 653 Publish As WebPage dialog box
xlDialogPushbuttonProperties 445 Pushbutton Properties dialog box
xlDialogReplaceFont 134 Replace Font dialog box
xlDialogRoutingSlip 336 Routing Slip dialog box
xlDialogRowHeight 127 Row Height dialog box
xlDialogRun 17 Run dialog box
xlDialogSaveAs 5 SaveAs dialog box
xlDialogSaveCopyAs 456 SaveCopyAs dialog box
xlDialogSaveNewObject 208 Save New Object dialog box
xlDialogSaveWorkbook 145 Save Workbook dialog box
xlDialogSaveWorkspace 285 Save Workspace dialog box
xlDialogScale 87 Scale dialog box
xlDialogScenarioAdd 307 Scenario Add dialog box
xlDialogScenarioCells 305 Scenario Cells dialog box
xlDialogScenarioEdit 308 Scenario Edit dialog box
xlDialogScenarioMerge 473 Scenario Merge dialog box
xlDialogScenarioSummary 311 Scenario Summary dialog box
xlDialogScrollbarProperties 420 Scrollbar Properties dialog box
xlDialogSearch 731 Search dialog box
xlDialogSelectSpecial 132 Select Special dialog box
xlDialogSendMail 189 Send Mail dialog box
xlDialogSeriesAxes 460 Series Axes dialog box
xlDialogSeriesOptions 557 Series Options dialog box
xlDialogSeriesOrder 466 Series Order dialog box
xlDialogSeriesShape 504 Series Shape dialog box
xlDialogSeriesX 461 Series X dialog box
xlDialogSeriesY 462 Series Y dialog box
xlDialogSetBackgroundPicture 509 Set Background Picture dialog box
xlDialogSetPrintTitles 23 Set Print Titles dialog box
xlDialogSetUpdateStatus 159 Set Update Status dialog box
xlDialogShowDetail 204 Show Detail dialog box
xlDialogShowToolbar 220 Show Toolbar dialog box
xlDialogSize 261 Size dialog box
xlDialogSort 39 Sort dialog box
xlDialogSortSpecial 192 Sort Special dialog box
xlDialogSplit 137 Split dialog box
xlDialogStandardFont 190 Standard Font dialog box
xlDialogStandardWidth 472 Standard Width dialog box
xlDialogStyle 44 Style dialog box
xlDialogSubscribeTo 218 Subscribe To dialog box
xlDialogSubtotalCreate 398 Subtotal Create dialog box
xlDialogSummaryInfo 474 Summary Info dialog box
xlDialogTable 41 Table dialog box
xlDialogTabOrder 394 Tab Order dialog box
xlDialogTextToColumns 422 Text To Columns dialog box
xlDialogUnhide 94 Unhide dialog box
xlDialogUpdateLink 201 Update Link dialog box
xlDialogVbaInsertFile 328 VBA Insert File dialog box
xlDialogVbaMakeAddin 478 VBA Make Addin dialog box
xlDialogVbaProcedureDefinition 330 VBA Procedure Definition dialog box
xlDialogView3d 197 View 3D dialog box
xlDialogWebOptionsBrowsers 773 Web Options Browsers dialog box
xlDialogWebOptionsEncoding 686 Web Options Encoding dialog box
xlDialogWebOptionsFiles 684 Web Options Files dialog box
xlDialogWebOptionsFonts 687 Web Options Fonts dialog box
xlDialogWebOptionsGeneral 683 Web Options General dialog box
xlDialogWebOptionsPictures 685 Web Options Pictures dialog box
xlDialogWindowMove 14 Window Move dialog box
xlDialogWindowSize 13 Window Size dialog box
xlDialogWorkbookAdd 281 Workbook Add dialog box
xlDialogWorkbookCopy 283 Workbook Copy dialog box
xlDialogWorkbookInsert 354 Workbook Insert dialog box
xlDialogWorkbookMove 282 Workbook Move dialog box
xlDialogWorkbookName 386 Workbook Name dialog box
xlDialogWorkbookNew 302 Workbook New dialog box
xlDialogWorkbookOptions 284 Workbook Options dialog box
xlDialogWorkbookProtect 417 Workbook Protect dialog box
xlDialogWorkbookTabSplit 415 Workbook Tab Split dialog box
xlDialogWorkbookUnhide 384 Workbook Unhide dialog box
xlDialogWorkgroup 199 Workgroup dialog box
xlDialogWorkspace 95 Workspace dialog box
xlDialogZoom 256 Zoom dialog box

To download an example workbook CLICK HERE

Leave a Comment

Set Excel Document Properties

December 20, 2008 at 19:40 · Filed under VBA Examples

Somebody asked me whether it is possible to change the built document properties programmatically, that we can access for excel by going to worksheet menu bar… file >> properties… The Document Property window pops up and you can set the built in document properties by typing out on controls in summary tab. I thought it wont be possible, but then I found a way that this can be done.

I have listed the code below…

Option Explicit

Private Sub cmdAssign_Click()
Dim i As Integer
For i = 0 To Me.Controls.Count – 1
  If UCase$(TypeName(Controls(i))) = “TEXTBOX” Then
    With ActiveWorkbook
      .BuiltinDocumentProperties(Mid(Me.Controls(i).Name, 4, 255)) = Me.Controls(i).Text
      Me.Controls(i).Text = “”
    End With
  End If
Next i
MsgBox “Document Properties Successfully Done “
End Sub

Private Sub cmdCancel_Click()
Dim msg1
  msg1 = MsgBox(“Are you sure you want to quit this form?”, vbYesNo, “Exit This Form”)
  If msg1 = vbYes Then
    Unload Me
  End If
End Sub

To Download the example codes on userform CLICK HERE

To Download Example workbook Demonstrating the above code CLICK HERE

Leave a Comment

Check if a File name is more than 90 days old, & Delete

December 10, 2008 at 00:04 · Filed under VBA, VBA Examples

Selva V Pasupathy, HSBC Global Resourcing, Hyderabad

Option Explicit
‘___________________________________________________________________________
‘***************************************************************************
‘* MODULE NAME:     CODE TO COMPARE IF ANY FILENAME IS > 90 DAYS OLD
‘* AUTHOR:          Selva V Pasupathy, HSBC Global Resourcing, Hyderabad
‘*
‘*
‘* CONTACT:         socko@rediffmail.com
‘* WEB SITE:        http://socko.wordpress.com
‘* NOTES:
‘*  ————————————————————————
‘*  ————————————————————————
‘*
‘*___________________________________________________________________________
‘***************************************************************************

‘ CODE TO COMPARE IF ANY FILENAME IS > 90 DAYS OLD

Const Files_Location = “J:\Falcon\Management\INFILL REPORTS\FCD\”

Sub Delete_Files(ByRef sDir As String)
  Dim i As Integer
  Dim dt2Delete, strDT As Date
  
  With Application.FileSearch
    .LookIn = sDir
    .SearchSubFolders = False
    .FileType = msoFileTypeExcelWorkbooks
    .Execute
  End With
      
  With Application.FileSearch
    If .Execute() > 0 Then
      MsgBox “There were ” & .FoundFiles.Count & _
          ” file(s) found.”
        For i = 1 To .FoundFiles.Count
          MsgBox .FoundFiles(i)
          strDT = Mid(.FoundFiles(i), _
          InStrRev(.FoundFiles(i), ” “, _
          -1, vbTextCompare) + 1, 255)
          
          dt2Delete = Format(Now() – 90, “ddmmyyyy”)
          MsgBox dt2Delete
          If strDT < dt2Delete Then
              MsgBox strDT
              MsgBox “NEED TO DELETE”
          Else
              MsgBox “NEED NOT DELETE”
          End If
        Next i
      Else
          MsgBox “There were no files found.”
      End If
  End With

End Sub

Sub testDeleteFiles()
Call Delete_Files(Files_Location)
End Sub

Leave a Comment

Access Resources

December 5, 2008 at 23:47 · Filed under Access, Database, Knowledge Base, Links

Selva V Pasupathy, HSBC Global Resourcing, Hyderabad

  1. Access Junkie (Jeff Conrad)

  2. Access Junkie (Jeff Conrad, Microsoft)

  3. Access Links, by Tom Wickerath

  4. Access MVP

  5. Access RunCommand Constants

  6. Access Team Blog

  7. Access-Freak (Oliver Stohr, freakazeud)

  8. Alex Dybenko

  9. Allen Browne’s tips for Microsoft Access

  10. Arvin Meyer

  11. Bob Larson’s Quick Tutorials

  12. Clint Covington: Software design, Microsoft Office Access

  13. Converting to Access 2007

  14. Database Solutions for Microsoft Access

  15. Dealing With The Trust Center (Access 2007)

  16. Jeff Conrad’s resources page

  17. Luke Chung and FMS Free Resources

  18. Lynn Trapp’s LT Computer Designs

  19. Microsoft Access Database Tools, Code Samples for the serious developer, by Bill Mosca

  20. Microsoft Communities

  21. Microsoft KnowledgeBase Search Page

  22. MVP Allen Browne’s tutorials

  23. Q-Built Solutions Web site for Microsoft Access

  24. Relational Database Design Articles, Tutorials and Tips

  25. Roger’s Access Library

  26. Stephen Lebans Website

  27. Steve Schapel’s Assorted Access Tips

  28. The Access Web

  29. The Access Web resources page

  30. Tina’s Tips to Get You Going

  31. Tom Wickerath’s page

  32. Tony’s Main Microsoft Access Page

  33. Tony’s Microsoft Access Blog

  34. Utter Access

  35. Utter Angel’s MS Access Downloads (Candace Tripp)

  36. Viescas Consulting Links & Downloads

  37. Yahoo! Tech Groups — MS Access Professionals

Leave a Comment