{"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":"","format":"standard","meta":{"inline_featured_image":false,"_exactmetrics_skip_tracking":false,"_exactmetrics_sitenote_active":false,"_exactmetrics_sitenote_note":"","_exactmetrics_sitenote_category":0,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_feature_clip_id":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_post_was_ever_published":false},"categories":[114],"tags":[121,130],"class_list":["post-72","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-snippet","tag-excel","tag-vbe"],"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":35,"url":"https:\/\/keithratner.live\/optionexplicit\/export-modules-to-folder\/","url_meta":{"origin":72,"position":0},"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. Pass UseTimestamp:=True to put each export in its own timestamped subfolder so repeated runs don't overwrite earlier exports.\u2026","rel":"","context":"In &quot;Snippet&quot;","block_context":{"text":"Snippet","link":"https:\/\/keithratner.live\/optionexplicit\/category\/snippet\/"},"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":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":"In &quot;Snippet&quot;","block_context":{"text":"Snippet","link":"https:\/\/keithratner.live\/optionexplicit\/category\/snippet\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":190,"url":"https:\/\/keithratner.live\/optionexplicit\/deletebottomrowsfromworksheet\/","url_meta":{"origin":72,"position":2},"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;Snippet&quot;","block_context":{"text":"Snippet","link":"https:\/\/keithratner.live\/optionexplicit\/category\/snippet\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":65,"url":"https:\/\/keithratner.live\/optionexplicit\/deleteallfilesinfolder\/","url_meta":{"origin":72,"position":3},"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;Snippet&quot;","block_context":{"text":"Snippet","link":"https:\/\/keithratner.live\/optionexplicit\/category\/snippet\/"},"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":52,"url":"https:\/\/keithratner.live\/optionexplicit\/notes-on-style-vba-coding-style\/","url_meta":{"origin":72,"position":4},"title":"Notes on VBA Coding Style: Maximizing Scalability and Readability","author":"Keith","date":"April 4, 2019","format":false,"excerpt":"Establishing and adhering to a VBA coding style guide enables increased project reusability and scalability. It makes code more readable and, by extension, the coding experience far more enjoyable. Minimize Horizontal Scrolling Split lines (use the underscore!) and indent. My rationale is that horizontal scrolling takes too long. You want\u2026","rel":"","context":"In &quot;Article&quot;","block_context":{"text":"Article","link":"https:\/\/keithratner.live\/optionexplicit\/category\/article\/"},"img":{"alt_text":"Notes on Style","src":"https:\/\/i0.wp.com\/keithratner.live\/optionexplicit\/wp-content\/uploads\/sites\/29\/2019\/04\/notes-on-style.png?fit=724%2C962&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/keithratner.live\/optionexplicit\/wp-content\/uploads\/sites\/29\/2019\/04\/notes-on-style.png?fit=724%2C962&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/keithratner.live\/optionexplicit\/wp-content\/uploads\/sites\/29\/2019\/04\/notes-on-style.png?fit=724%2C962&ssl=1&resize=525%2C300 1.5x, https:\/\/i0.wp.com\/keithratner.live\/optionexplicit\/wp-content\/uploads\/sites\/29\/2019\/04\/notes-on-style.png?fit=724%2C962&ssl=1&resize=700%2C400 2x"},"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;Snippet&quot;","block_context":{"text":"Snippet","link":"https:\/\/keithratner.live\/optionexplicit\/category\/snippet\/"},"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}]}}