Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Jan 2016
    Posts
    77
    Thanks
    18
    Thanked 1 Time in 1 Post

    Bulk Formatting Userform Controls - Command Buttons, Labels & Text Boxes

    Hello Folks,

    hope all are great!

    I've got loads of command buttons and labels in a user form. Can some one help me sort this code out

    How do I format all the command buttons and labels so they have a consistent format.

    Am I on the right track.


    I also found this http://answers.microsoft.com/en-us/o...c88f54a?auth=1 and couldn't get that to work.


    Code:
    Sub FormatControls()
    
    
    Dim Ctrl            As control
    
        For Each Ctrl In CUserForm1.Controls
            If TypeName(Ctrl) = "CommandButton" Then
            Ctrl.BackColor = 55295
            Ctrl.font.size =14
            Ctrl.font.name ="Arial"
         
            etc
            
            
            
            End If
        Next

    Nothing seems to work.

    I have also looked all over just for this one - should be simple feat - but nothing that works when i try to code it. I tried a class module but that failed to work. .

    It needs some help from the expert pro now - if I may

    Many thanks for your time.

    Pb

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    PB,

    Here's a sample of what you can do.

    UserFormBefore.PNG

    Code:
    Option Explicit
    
    Private Sub UserForm_Initialize()
    
      Dim Ctrl As Control
      Dim iLeftMargin As Integer
      Dim iHeight     As Integer
      Dim iWidth      As Integer
      Dim iSpacing    As Integer
      Dim iNextTop    As Integer  'Next button top
      
      iLeftMargin = 15
      iHeight = 25
      iWidth = 40
      iSpacing = 5
      iNextTop = 10
    
      For Each Ctrl In ufTest.Controls
            If TypeName(Ctrl) = "CommandButton" Then
              With Ctrl
                  .BackColor = 55295
                  .Font.Size = 14
                  .Font.Name = "Arial"
                  .Top = iNextTop
                  .Left = iLeftMargin
                  .Height = iHeight
                  .Width = iWidth
                  iNextTop = iNextTop + iHeight + iSpacing
              End With
            End If
      Next
      
      With ufTest
          .Height = iNextTop + 20
          .Width = iWidth + 20
      End With 'ufTest
    
    End Sub  'UserForm_Load
    UserFormAfter.PNG

    Test File: Excel - VBA - Self Formatting UserForm.xlsm

    Note: There is a Macro in the test file to show the form just press Alt+F8 and select it. Also the code above is in the Userform object in the VBE.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    pb89 (2016-02-19)

  4. #3
    Star Lounger
    Join Date
    Jan 2016
    Posts
    77
    Thanks
    18
    Thanked 1 Time in 1 Post
    RG,

    thank you very much for this work.

    I just don't know whats wrong with google these days.

    They are happy to give you everything that is outdated and wrong. Been driving me up the wall. All this hoo ha over some formatting -

    Ok so I understand that I need to put the code in the UserForm_Initialize.

    Private Sub UserForm_Initialize()




    Let me play and report back just in case there is something I need that tiny help with,

    Coffee for you RG


    Many thanks again

    pb

  5. #4
    Star Lounger
    Join Date
    Jan 2016
    Posts
    77
    Thanks
    18
    Thanked 1 Time in 1 Post
    Hi RG,

    learned a new trick just now

    Had 2 of the same user form initialize doing different tasks - So I made them separate macros and called them from a main initialize one.


    Code:
    Private Sub UserForm_Initialize()
    
        CUserForm_Initialize
        CreateMenu
       
        
    End Sub
    The command buttons are being formatted nicely - just need to finish off the labels

    I know baby steps - but you see - you can't hurry experience.


    pb

  6. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Quote Originally Posted by pb89 View Post
    I know baby steps - but you see - you can't hurry experience. pb
    PB, you got that right!
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #6
    Star Lounger
    Join Date
    Jan 2016
    Posts
    77
    Thanks
    18
    Thanked 1 Time in 1 Post
    Hi folks,


    I also found this that deals with the designer canvas,

    http://www.vbaexpress.com/forum/show...-in-a-userform

    So both problems solved.

    One for the main bulky canvas interface and RG's solution for the run time design

    thanks again RG


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •