{"id":72,"date":"2019-04-07T22:03:22","date_gmt":"2019-04-07T22:03:22","guid":{"rendered":"https:\/\/keithratner.live\/optionexplicit\/?p=72"},"modified":"2019-04-26T21:02:46","modified_gmt":"2019-04-26T21:02:46","slug":"renderlistcontrolheadings","status":"publish","type":"post","link":"https:\/\/keithratner.live\/optionexplicit\/renderlistcontrolheadings\/","title":{"rendered":"RenderListControlHeadings"},"content":{"rendered":"<pre>Public Sub RenderListControlHeadings( _\r\n    ListControl As Control, _\r\n    ColumnHeadingString As String, _\r\n    Optional FontWeight As Long = 400, _\r\n    Optional OffsetLeft As Long = 8 _\r\n)\r\n    Dim ctls As Controls\r\n    Dim iCtl As Control\r\n    Dim strControlNameStub As String\r\n    Dim strLabelNameStub As String\r\n    Set ctls = ListControl.Parent.Controls\r\n    strLabelNameStub = \"lbheading_\"\r\n    strControlNameStub = _\r\n        strLabelNameStub & _\r\n        ListControl.Name\r\n    Set ctls = ListControl.Parent.Controls\r\n    For Each iCtl In ctls\r\n        If Left( _\r\n            iCtl.Name, _\r\n            Len( _\r\n                strControlNameStub _\r\n            ) _\r\n        ) = strControlNameStub Then\r\n            ctls.Remove iCtl.Name\r\n        End If\r\n    Next iCtl\r\n    If ColumnHeadingString = \"\" Then Exit Sub\r\n    Dim arrWidths As Variant\r\n    Dim arrHeadings As Variant\r\n    arrWidths = Split( _\r\n        ListControl.ColumnWidths, _\r\n        \";\" _\r\n    )\r\n    arrHeadings = Split( _\r\n        ColumnHeadingString, _\r\n        \";\" _\r\n    )\r\n    Dim i As Integer\r\n    Dim accLeftPosition As Long\r\n    accLeftPosition = ListControl.Left + OffsetLeft\r\n    For i = 0 To UBound(arrHeadings)\r\n        With ctls.Add( _\r\n            \"Forms.Label.1\", _\r\n            strControlNameStub & i _\r\n        )\r\n            .Caption = arrHeadings(i)\r\n            .Left = accLeftPosition\r\n            .Top = ListControl.Top - 12\r\n            .Font.Size = 7\r\n            .Font.Weight = FontWeight\r\n            .BackStyle = fmBackStyleTransparent\r\n        End With\r\n        accLeftPosition = _\r\n            accLeftPosition + _\r\n            CLng( _\r\n                Trim( _\r\n                    Replace( _\r\n                        arrWidths(i), _\r\n                        \" pt\", \"\" _\r\n                    ) _\r\n                ) _\r\n            )\r\n    Next i\r\nEnd Sub<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Public Sub RenderListControlHeadings( _ ListControl As Control, _ ColumnHeadingString As String, _ Optional FontWeight As Long = 400, _ Optional OffsetLeft As Long = 8 _ ) Dim ctls As Controls Dim iCtl As Control Dim strControlNameStub As String Dim strLabelNameStub As String Set ctls = ListControl.Parent.Controls strLabelNameStub = &#8220;lbheading_&#8221; strControlNameStub = _ strLabelNameStub &#038; [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":80,"comment_status":"open","ping_status":"open","sticky":false,"template":"page-templates\/full-width-no-post-thumbnail.php","format":"standard","meta":{"inline_featured_image":false,"_exactmetrics_skip_tracking":false,"_exactmetrics_sitenote_active":false,"_exactmetrics_sitenote_note":"","_exactmetrics_sitenote_category":0,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[5,6,3],"tags":[],"class_list":["post-72","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-code","category-snippets","category-vba"],"krwpengineoptions-featuredonwpengineblog":"","jetpack_featured_media_url":"https:\/\/i0.wp.com\/keithratner.live\/optionexplicit\/wp-content\/uploads\/sites\/29\/2019\/04\/renderlistcontrolheadings.png?fit=576%2C1008&ssl=1","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/pgsIIA-1a","jetpack-related-posts":[{"id":965,"url":"https:\/\/keithratner.live\/optionexplicit\/export-vba-modules-for-version-control\/","url_meta":{"origin":72,"position":0},"title":"Export VBA Modules for Version Control updated September 2024","author":"Keith","date":"September 26, 2024","format":false,"excerpt":"Add a Module in your VBA Project. Name it \"Exports\" and paste the following code into the module in its entirety. When your project is ready for export (and subsequent version control), click on the \"Macros\" button in the Developer ribbon, select \"ExportVBAModules\" and click \"Run.\" Option Explicit ' Define\u2026","rel":"","context":"In &quot;Articles&quot;","block_context":{"text":"Articles","link":"https:\/\/keithratner.live\/optionexplicit\/category\/articles\/"},"img":{"alt_text":"Exports Module","src":"https:\/\/i0.wp.com\/keithratner.live\/optionexplicit\/wp-content\/uploads\/sites\/29\/2024\/09\/exports-module.png?fit=885%2C424&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/keithratner.live\/optionexplicit\/wp-content\/uploads\/sites\/29\/2024\/09\/exports-module.png?fit=885%2C424&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/keithratner.live\/optionexplicit\/wp-content\/uploads\/sites\/29\/2024\/09\/exports-module.png?fit=885%2C424&ssl=1&resize=525%2C300 1.5x, https:\/\/i0.wp.com\/keithratner.live\/optionexplicit\/wp-content\/uploads\/sites\/29\/2024\/09\/exports-module.png?fit=885%2C424&ssl=1&resize=700%2C400 2x"},"classes":[]},{"id":612,"url":"https:\/\/keithratner.live\/optionexplicit\/compresspageranges\/","url_meta":{"origin":72,"position":1},"title":"CompressPageRanges","author":"Keith","date":"May 24, 2024","format":false,"excerpt":"Function CompressPageRanges(ByVal pageList As String) As String Dim pages() As String Dim i As Long Dim result As String Dim startPage As Long Dim endPage As Long pages = Split(pageList, \",\") For i = 0 To UBound(pages) If startPage = 0 Then startPage = CLng(pages(i)) endPage = startPage ElseIf CLng(pages(i))\u2026","rel":"","context":"Similar post","block_context":{"text":"Similar post","link":""},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":35,"url":"https:\/\/keithratner.live\/optionexplicit\/export-modules-to-folder\/","url_meta":{"origin":72,"position":2},"title":"Export Modules to Folder","author":"Keith","date":"April 3, 2019","format":false,"excerpt":"This will create a folder using the base name of your Excel file, which is the filename without its extension, along with a vba subfolder. Your VBA modules will be placed there. Set Reference to Microsoft Visual Basic for Applications Extensibility Sub ExportModules( _ Optional PathToVBAModules As String = \"\"\u2026","rel":"","context":"In &quot;Code&quot;","block_context":{"text":"Code","link":"https:\/\/keithratner.live\/optionexplicit\/category\/code\/"},"img":{"alt_text":"Export Modules","src":"https:\/\/i0.wp.com\/keithratner.live\/optionexplicit\/wp-content\/uploads\/sites\/29\/2019\/04\/4-4-2019-2-16-36-AM.png?fit=665%2C496&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/keithratner.live\/optionexplicit\/wp-content\/uploads\/sites\/29\/2019\/04\/4-4-2019-2-16-36-AM.png?fit=665%2C496&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/keithratner.live\/optionexplicit\/wp-content\/uploads\/sites\/29\/2019\/04\/4-4-2019-2-16-36-AM.png?fit=665%2C496&ssl=1&resize=525%2C300 1.5x"},"classes":[]},{"id":190,"url":"https:\/\/keithratner.live\/optionexplicit\/deletebottomrowsfromworksheet\/","url_meta":{"origin":72,"position":3},"title":"DeleteBottomRowsFromWorksheet","author":"Keith","date":"August 14, 2019","format":false,"excerpt":"Note: This method depends on GetLastUsedRowNumberInWorksheet Sub DeleteBottomRowsFromWorksheet( _ ws As Worksheet _ ) Dim _ strStartingEmptyRow As String, _ rngStartingRow As Range, _ rngEmptyRows As Range, _ lngLastRow As Long lngLastRow = _ GetLastUsedRowNumberInWorksheet( _ ws _ ) If lngLastRow = _ -1 Then Exit Sub End If strStartingEmptyRow\u2026","rel":"","context":"In &quot;Code&quot;","block_context":{"text":"Code","link":"https:\/\/keithratner.live\/optionexplicit\/category\/code\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":65,"url":"https:\/\/keithratner.live\/optionexplicit\/deleteallfilesinfolder\/","url_meta":{"origin":72,"position":4},"title":"DeleteAllFilesInFolder","author":"Keith","date":"April 4, 2019","format":false,"excerpt":"Public Sub DeleteAllFilesInFolder( _ strPathToFolder As String _ ) Dim _ fs As Object, _ fldr As Object, _ f As Object Set fs = CreateObject(\"Scripting.FileSystemObject\") Set fldr = fs.GetFolder(strPathToFolder) For Each f In fldr.Files Application.StatusBar = _ \"Deleting file \" & _ f.Name & _ \" from folder \"\u2026","rel":"","context":"In &quot;Code&quot;","block_context":{"text":"Code","link":"https:\/\/keithratner.live\/optionexplicit\/category\/code\/"},"img":{"alt_text":"DeleteAllFilesInFolder","src":"https:\/\/i0.wp.com\/keithratner.live\/optionexplicit\/wp-content\/uploads\/sites\/29\/2019\/04\/deleteallfilesinfolder.png?fit=389%2C343&ssl=1&resize=350%2C200","width":350,"height":200},"classes":[]},{"id":113,"url":"https:\/\/keithratner.live\/optionexplicit\/slicercount\/","url_meta":{"origin":72,"position":5},"title":"SlicerCount","author":"Keith","date":"April 9, 2019","format":false,"excerpt":"Public Function SlicerCount( _ scl As SlicerCacheLevel, _ Optional NonBlankOnly As Boolean = True, _ Optional Verbose As Boolean = True _ ) Dim si As SlicerItem Dim iCount As Integer Dim iVerboseCount As Integer Dim iTotal As Integer iCount = 0 If Verbose Then iVerboseCount = 0 iTotal =\u2026","rel":"","context":"In &quot;Code&quot;","block_context":{"text":"Code","link":"https:\/\/keithratner.live\/optionexplicit\/category\/code\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/keithratner.live\/optionexplicit\/wp-content\/uploads\/sites\/29\/2019\/04\/slicercount.png?fit=332%2C568&ssl=1&resize=350%2C200","width":350,"height":200},"classes":[]}],"_links":{"self":[{"href":"https:\/\/keithratner.live\/optionexplicit\/wp-json\/wp\/v2\/posts\/72","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/keithratner.live\/optionexplicit\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/keithratner.live\/optionexplicit\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/keithratner.live\/optionexplicit\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/keithratner.live\/optionexplicit\/wp-json\/wp\/v2\/comments?post=72"}],"version-history":[{"count":2,"href":"https:\/\/keithratner.live\/optionexplicit\/wp-json\/wp\/v2\/posts\/72\/revisions"}],"predecessor-version":[{"id":141,"href":"https:\/\/keithratner.live\/optionexplicit\/wp-json\/wp\/v2\/posts\/72\/revisions\/141"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/keithratner.live\/optionexplicit\/wp-json\/wp\/v2\/media\/80"}],"wp:attachment":[{"href":"https:\/\/keithratner.live\/optionexplicit\/wp-json\/wp\/v2\/media?parent=72"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/keithratner.live\/optionexplicit\/wp-json\/wp\/v2\/categories?post=72"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/keithratner.live\/optionexplicit\/wp-json\/wp\/v2\/tags?post=72"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}