Add Label to Userform Programmatically

Leave a Comment

Restrict entry into Textbox Control

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

– 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:        https://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

TED: Ideas worth spreading

by Selva V Pasupathy, HSBC Technology & Services, Hyderabad.
I incidentally happened to visit a http://www.ted.com and I found that it was wonderful. TED (Technology, Entertainment, Design) is an invitation-only event where the world’s leading thinkers and doers gather to find inspiration. I viewed some of the video’s by Tony Robbins, which were really good. I hope someone benefits from these links.

  1. A 3-minute story of mixed emoticons
  2. A 4-minute medley on the music wars
  3. A beautiful new theory of everything
  4. A brief digression on matters of lost time
  5. A brief history of violence
  6. A cello performance that casts a spell
  7. A comic send-up of TED2006
  8. A different way to think about creative genius
  9. A digital library, free to the world
  10. A girl, a photograph, a homecoming
  11. A guided tour of the Ghost Map
  12. A happy song about global warming
  13. A hero of the Congo Basin forest
  14. A journey to the center of your mind
  15. A library of human imagination
  16. A life of fascinations
  17. A lyrical view of life on Earth
  18. A meditation on hope
  19. A mockingbird remix of TED2006
  20. A musical sensation from Venezuela
  21. A new vision for refrigeration
  22. A parable for Kenya
  23. A performance merging dance and biology
  24. A powerful idea about teaching ideas
  25. A secular, scientific rebuttal to Rick Warren
  26. A surprising idea for “solving” climate change
  27. A surprising look at celebrity
  28. A surprising parable of foie gras
  29. A vision for TED
  30. A voyage of DNA, genes and the sea
  31. Africa as an investment
  32. African fractals, in buildings and braids
  33. After the gold rush, there’s innovation ahead
  34. All roads lead to Rome Antics
  35. An atheist’s call to arms
  36. An inside tour of the world’s biggest supercollider
  37. An introduction to genomics
  38. Ants, terrorism, and the awesome power of memes
  39. Apes that write, start fires and play Pac-Man
  40. Architecture is a new way to connect to the world
  41. Architecture is a special effects machine
  42. Architecture, modern and romantic
  43. Are children’s carseats necessary?
  44. Art with wire, thread, sugar, chocolate
  45. Asking big questions about the universe
  46. Beauty and truth in physics
  47. Becoming Buddha — on the Web
  48. Beyond the crisis, mindboggling science and the arrival of Homo evolutis
  49. Brain magic
  50. Brain science is about to fundamentally change computing
  51. Breath, music, passion
  52. Bringing One Laptop per Child to Colombia: TED in the Field
  53. Bringing world-class health care to the poorest
  54. Building a commodities market in Ethiopia
  55. Building a family tree for all humanity
  56. Building a home for the Clock of the Long Now
  57. Building on the green agenda
  58. Building the Ground Zero viewing platform
  59. BumpTop desktop is a beautiful mess
  60. Can kids teach themselves?
  61. Can we domesticate germs?
  62. Can we know our own minds?
  63. Casting spells with DNA
  64. Catch Sputnik mania!
  65. Celebrating the camel
  66. Celebrating the scientific experiment
  67. Chalking it up to the blank slate
  68. Cheetahs vs. Hippos for Africa’s future
  69. Classical music with shining eyes
  70. Climbing the world’s biggest trees
  71. Close-up card magic
  72. Come play with Pleo the dinosaur
  73. Creating objects that tell stories
  74. Creating tech marvels out of a $40 Wii Remote
  75. Creativity, fulfillment and flow
  76. Cultures at the far edge of the world
  77. Dazzling set by 11-year-old violinist
  78. Debunking third-world myths with the best stats you’ve ever seen
  79. Decoding the future with genomics
  80. Design and the elastic mind
  81. Design is in the details
  82. Design, discovery and humor
  83. Designing the Seattle Central Library
  84. Digging for humanity’s origins
  85. Do all languages have a common ancestor?
  86. Do schools kill creativity?
  87. Do the green thing
  88. Documenting our endangered cultures
  89. Does happiness have a price tag?
  90. Dog-friendly dog training
  91. Earth in its final century?
  92. Earth’s mass extinctions
  93. Educating a new generation of African leaders
  94. Entrepreneurs are the future of space flight
  95. Exploring the frontiers of happiness
  96. Exploring the ocean’s hidden worlds
  97. Fantastic voyage inside a cell
  98. Fashion, passion, and about a million other things
  99. Fiddling in reel time
  100. Fighting injustice with a videocamera
  101. Finding happiness in body and soul
  102. Finding the origins of humanity
  103. Fly me to the moons of Saturn
  104. Fly the seas on a submarine with wings
  105. Flying on solar wings
  106. Four American characters
  107. From 1984, 4 predictions about the future (3 of them correct)
  108. From 1990, defending a vision for architecture
  109. Getting cars off the road and data into the skies
  110. Goodbye, textbooks; hello, open-source learning
  111. Great cars are Art
  112. Great design is serious (not solemn)
  113. Great ideas for finding new energy
  114. Greening the ghetto
  115. Habits of happiness
  116. Hardware solutions to everyday problems
  117. Healing and other natural wonders
  118. Health, population and the human mind
  119. Heartrending pictures of AIDS
  120. Help fight local warming
  121. Help me bring music to kids worldwide (TED Prize winner!)
  122. Helping humans and animals live together in Africa
  123. Here’s how to protect the blue heart of the planet (TED Prize winner!)
  124. Hip-hop dance and a little magic
  125. How a ragtag band created Wikipedia
  126. How Benjamin Button got his face
  127. How blogs are building a friendlier world
  128. How calculus is changing architecture
  129. How cooperation (eventually) trumps conflict
  130. How could God have allowed the tsunami?
  131. How creativity is being strangled by the law
  132. How do ants know what to do?
  133. How does technology evolve? Like we did
  134. How engineers learn from evolution
  135. How great design makes ideas new
  136. How I built my family a windmill
  137. How I got my new hip
  138. How I’m trying to change the world now
  139. How juries are fooled by statistics
  140. How ordinary people become monsters … or heroes
  141. How robots will invade our lives
  142. How science is like democracy
  143. How technology’s accelerating power will transform us
  144. How things in nature tend to sync up
  145. How to fix broken states
  146. How to help Africa? Do business there
  147. How to listen to music with your whole body
  148. How to survive a nuclear attack
  149. How would you feel if you lost everything?
  150. Humanity’s biggest problems aren’t what you think they are
  151. Hunting the next killer virus
  152. I walk the Earth
  153. Idea + square = origami
  154. Inside the Google machine
  155. Inspired ideas for a sustainable future
  156. Institutions vs. collaboration
  157. Inventing the next amazing thing
  158. Investing in Africa’s own solutions
  159. Is 4 a.m. the new midnight?
  160. Jaw-dropping Photosynth demo
  161. Journey to the center of the Earth … and beyond!
  162. Juggling rhythm and motion
  163. Learning from past presidents in moments of crisis
  164. Learning the stories of Africa
  165. Let’s have a deeper discussion on aid
  166. Let’s look for life in the outer solar system
  167. Let’s take a new look at African aid
  168. Let’s take a nuclear-powered rocket to Saturn
  169. Leveraging big ideas to make change
  170. Life at 30,000 feet
  171. Life on Mars? Let’s look in the caves
  172. Lightning calculation and other “Mathemagic”
  173. Living a life of purpose
  174. Look! Up in the sky! It’s Virtual Earth!
  175. Looking inside the brain in real time
  176. Magical improv from 14-year-old pianist
  177. Making a computer that works like the brain
  178. Making movies that make change
  179. Measuring the fastest animal on earth
  180. Memes and “temes”
  181. My dream about the future of medicine
  182. My history of electroshock therapy
  183. My journey in design, from tofu to RISD
  184. My stroke of insight
  185. My year of living biblically
  186. Nature vs. humans, and what we can do about it
  187. New insights on poverty and life around the world
  188. New prosthetic arm for veterans
  189. New thinking on the climate crisis
  190. Nice building. Then what?
  191. On the verge of creating synthetic life
  192. One Laptop per Child, two years on
  193. Open-source economics
  194. Origami, blindfolded and to music
  195. Our cell phones, ourselves
  196. Our priorities for saving the world
  197. Photography connects us with the world
  198. Picturing excess
  199. Playing the Cape Breton fiddle
  200. Politics and religion are technologies
  201. Predicting the next 5,000 days of the web
  202. Presenting the Orb
  203. Products (and toys) from the future
  204. Rebuilding America, one slide show at a time
  205. Re-creating great performances
  206. Redefining the dictionary
  207. Reinventing the car
  208. Reinventing the school lunch
  209. Releasing the music in your head
  210. Rethinking the music video
  211. Rich hospital, poor hospital
  212. Robots that are “self-aware”
  213. Rolling along, helping students and the third world
  214. Running on high-tech legs
  215. Sailing the Great Pacific Garbage Patch
  216. Scenes from “The War Tapes”
  217. Sculpture that’s truly moving
  218. Second Life, where anything is possible
  219. Secrets of movement, from geckos and roaches
  220. Secrets of success in 8 words, 3 minutes
  221. Security and insecurity
  222. Seeking salvation and profit in greentech
  223. Shaking hands with the devil
  224. Siftables, the toy blocks that think
  225. Simple designs that could save millions of childrens’ lives
  226. Simplicity patterns
  227. Sing a song of sustainable cities
  228. Sliced bread and other marketing delights
  229. Slowing down in a world built for speed
  230. Sound stylings by a human beatbox
  231. Spinning a story of Mama
  232. Stephen Hawking hits zero g
  233. Swim with giant sunfish in the open ocean
  234. Tackling poverty with “patient capital”
  235. Take a ride in the Skycar
  236. Taking the next giant leap in space
  237. Tales of passion
  238. Talking and squawking TED2006
  239. Technology, faith and human shortcomings
  240. Technology’s Long Tail
  241. TED Prize wish: Finding new cures for migraine, depression, malpractice
  242. TED Prize wish: Help build the Encyclopedia of Life
  243. TED Prize wish: Help stop the next pandemic
  244. TED Prize wish: Join my call to action on Africa
  245. TED Prize wish: Let’s build a health care system in Rwanda
  246. TED Prize wish: Open-source architecture to house the world
  247. TED Prize wish: Share a vital story with the world
  248. TED Prize wish: Share the story of Earth’s manufactured landscapes
  249. TED Prize wish: Unite the world on Pangea Day, a global day of film
  250. Telling stories of our shared humanity
  251. The “shadow cities” of the future
  252. The amazing intelligence of crows
  253. The art of baking bread
  254. The art of collecting stories
  255. The art of creating creatures
  256. The astonishing promise of DNA folding
  257. The beckoning promise of personal fabrication
  258. The birth of the computer
  259. The brain in love
  260. The case for informed optimism
  261. The coming neurological epidemic
  262. The deep oceans: a ribbon of life
  263. The design of the universe
  264. The double helix and today’s DNA mysteries
  265. The future of design is human-centered
  266. The illustrated woman
  267. The Jill and Julia Show
  268. The joy of rockets
  269. The lost art of letter-writing
  270. The making of an African activist
  271. The moment when social media became the news
  272. The mystery box
  273. The omnivore’s next dilemma
  274. The paradox of choice
  275. The Pentagon’s new map for war and peace
  276. The playful search for beauty
  277. The power and beauty of organic design
  278. The power of glamour
  279. The power of saying thank you
  280. The power of the mobile phone to end poverty
  281. The power to connect the world
  282. The powerful link between creativity and play
  283. The real crisis? We stopped being wise
  284. The real difference between liberals and conservatives
  285. The rise of the amateur professional
  286. The science of love, and the future of women
  287. The science of scent
  288. The search for dark energy and dark matter
  289. The stories and song of Appalachia
  290. The story of a passionate life
  291. The story of Ezra, a child soldier
  292. The story of the Mars Rovers
  293. The stuff of thought
  294. The tragedy of suburbia
  295. The true face of Leonardo Da Vinci?
  296. The truly soft side of software
  297. The universe is queerer than we can suppose
  298. The universe on a string
  299. The vision behind One Laptop Per Child
  300. The Web and the city
  301. The Web and TV, a sibling rivalry
  302. The Web’s secret stories
  303. The wisdom of designing Cradle to Cradle
  304. The wonders of Zulu wire art
  305. The world now eats (and dies) like Americans
  306. The worldwide web of belief and ritual
  307. Theremin, the untouchable music
  308. Things I have learned in my life so far
  309. Three things to know before you ski to the North Pole
  310. Tidying up art
  311. Tools for building a better world
  312. Tourist snapshots from North Korea
  313. Toys that make worlds
  314. Treating design as art
  315. Understanding comics
  316. Underwater astonishments
  317. Unveiling the genius of multi-touch interface design
  318. Use my photographs to stop the worldwide XDR-TB epidemic
  319. Using biology to make better animation
  320. Wake up! It’s They Might Be Giants
  321. Way-new collaboration
  322. Ways of seeing
  323. We must win the oil endgame
  324. Welcome to Nollywood
  325. Welcome to Vaudeville 2.0
  326. What can fossils teach us?
  327. What do consumers really want?
  328. What do we really know about the spread of AIDS?
  329. What I’m worried about, what I’m excited about
  330. What is our place in the cosmos?
  331. What makes a building unique?
  332. What positive psychology can help you become
  333. What separates us from the apes?
  334. What we can learn from spaghetti sauce
  335. What’s so funny about the Web?
  336. What’s wrong with what we eat
  337. When it comes to tech, simplicity sells
  338. Where does creativity hide?
  339. Where have the bees gone?
  340. Who was General Tso? and other mysteries of American Chinese food
  341. Why a free press is the best investment
  342. Why are we happy? Why aren’t we happy?
  343. Why aren’t we all Good Samaritans?
  344. Why can’t we grow new body parts?
  345. Why can’t we grow new energy?
  346. Why design?
  347. Why do crack dealers still live with their moms?
  348. Why newspapers still matter (and why tech news belongs on the front page)
  349. Why people believe strange things
  350. Why societies collapse
  351. Why squatter cities are a good thing
  352. Why the search for alien intelligence matters (TED Prize winner!)
  353. Why we age and how we can avoid it
  354. Why we do what we do, and how we can do it better
  355. Why we don’t understand as much as we think we do
  356. Why we know less than ever about the world
  357. Will videogames become better than life?
  358. WorldWide Telescope
  359. Yes, design can make you happy
  360. Your brain is badly wired — enjoy it!
  361. Your genes are not your fate

Leave a Comment

Add Control to CommandBars in WorksheetMenuBar

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

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

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

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:        https://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

Leave a Comment

Older Posts »