Excel

Modify UserForm Appearances under Run-Time Conditions

Ease of Use

Intermediate

Version tested with

2000 (partial), X 

Submitted by:

BlueCactus

Description:

A tool to help you perfect UserForm appearance by recording modifications at run-time. Gives you i) table of form / control properties; ii) VBA code for modifications. 

Discussion:

A perfectly designed UserForm does not always appear perfect at run-time. Maybe your control values don't fit as expected, or some strange Office inconsistency kicks in to rearrange your masterpiece. This KB provides a method for modifying *some* form / control properties under run-time conditions (i.e., when the form is filled with real values). The results are returned to you in the form of i) a table of properties with modified values highlighted; ii) a snippet of VBA code for repeating your modifications. The VBA code can be particularly useful in a cross-platform situation where you don't want to change the original form, but you would like it to run with a different appearance on another platform. NOTE: Modifications are not permanent. To make them permanent, you must edit the form with the values in the property table, or incorporate the returned VBA code into your forms / modules. NOTE2: If you only want the form metrics, you might also be interested in: http://vbaexpress.com/kb/getarticle.php?kb_id=523 

Code:

instructions for use

			

//// //// UserForm Code - For best results use the example file //// Option Explicit Dim fm1ThisForm As Variant Dim fm1ThisControl As Control Dim fm1PasteProp As String, fm1PasteVal As Variant Dim fm1OldProps As Variant, fm1NewProps As Variant, fm1PropName As Variant Dim fm1SpinUnit As Variant Const fm1cdLeft As Integer = 1, fm1cdTop As Integer = 2, fm1cdWidth As Integer = 3, fm1cdHeight As Integer = 4 Const fm1cdFontName As Integer = 5, fm1cdFontSize As Integer = 6 Const fm1cdFontBold As Integer = 7, fm1cdFontItalic As Integer = 8, fm1cdFontUnderline As Integer = 9 Const fm1cdColWidths As Integer = 10 Const fm1cdTabWidth As Integer = 11, fm1cdTabHeight As Integer = 12 Const fm1cdMultiLine As Integer = 13, fm1cdTabPos As Integer = 14, fm1cdTabStyle As Integer = 15 Const fm1cdZoom As Integer = 16 Private Sub BoxColWidth_Change() On Error GoTo FailColWidth fm1ThisControl.ColumnWidths = BoxColWidth.Text On Error GoTo 0 BoxColWidth.ForeColor = 0 fm1NewProps(ListControls.ListIndex + 1, fm1cdColWidths) = fm1ThisControl.ColumnWidths Exit Sub FailColWidth: BoxColWidth.ForeColor = 255 On Error GoTo 0 End Sub Private Sub BoxFont_Change() fm1ThisControl.Font.Name = BoxFont.Text fm1NewProps(ListControls.ListIndex + 1, fm1cdFontName) = fm1ThisControl.Font.Name End Sub Private Sub BoxFontSize_Change() If validate(BoxFontSize.Text, "BoxFontSize") Then fm1ThisControl.Font.Size = Val(BoxFontSize.Text) fm1NewProps(ListControls.ListIndex + 1, fm1cdFontSize) = fm1ThisControl.Font.Size End Sub Private Sub BoxFormHeight_Change() If validate(BoxFormHeight.Text, "BoxFormHeight") Then fm1ThisForm.Height = Val(BoxFormHeight.Text) fm1NewProps(UBound(fm1NewProps, 1), fm1cdHeight) = fm1ThisForm.Height End Sub Private Sub BoxFormWidth_Change() If validate(BoxFormWidth.Text, "BoxFormWidth") Then fm1ThisForm.Width = Val(BoxFormWidth.Text) fm1NewProps(UBound(fm1NewProps, 1), fm1cdWidth) = fm1ThisForm.Width End Sub Private Sub BoxFormZoom_Change() If validate(BoxFormZoom.Text, "BoxFormZoom") Then fm1ThisForm.Zoom = Val(BoxFormZoom.Text) LabelFormSize.Caption = "corresponds to form " & Int(fm1ThisForm.Width * 0.01 * fm1ThisForm.Zoom) & " x " & Int(fm1ThisForm.Height * 0.01 * fm1ThisForm.Zoom) fm1NewProps(UBound(fm1NewProps, 1), fm1cdZoom) = fm1ThisForm.Zoom End Sub Private Sub BoxHeight_Change() If validate(BoxHeight.Text, "BoxHeight") Then fm1ThisControl.Height = Val(BoxHeight.Text) fm1NewProps(ListControls.ListIndex + 1, fm1cdHeight) = fm1ThisControl.Height End Sub Private Sub BoxLeft_Change() If validate(BoxLeft.Text, "BoxLeft") Then fm1ThisControl.Left = Val(BoxLeft.Text) fm1NewProps(ListControls.ListIndex + 1, fm1cdLeft) = fm1ThisControl.Left End Sub Private Sub BoxSpinUnit_Change() If validate(BoxSpinUnit.Text, "BoxSpinUnit") Then fm1SpinUnit = Val(BoxSpinUnit.Text) End Sub Private Sub BoxTabHeight_Change() If validate(BoxTabHeight.Text, "BoxTabHeight") Then fm1ThisControl.TabFixedHeight = Val(BoxTabHeight.Text) fm1NewProps(ListControls.ListIndex + 1, fm1cdTabHeight) = fm1ThisControl.TabFixedHeight End Sub Private Sub BoxTabWidth_Change() If validate(BoxTabWidth.Text, "BoxTabWidth") Then fm1ThisControl.TabFixedWidth = Val(BoxTabWidth.Text) fm1NewProps(ListControls.ListIndex + 1, fm1cdTabWidth) = fm1ThisControl.TabFixedWidth End Sub Private Sub BoxTop_Change() If validate(BoxTop.Text, "BoxTop") Then fm1ThisControl.Top = Val(BoxTop.Text) fm1NewProps(ListControls.ListIndex + 1, fm1cdTop) = fm1ThisControl.Top End Sub Private Sub BoxWidth_Change() If validate(BoxWidth.Text, "BoxWidth") Then fm1ThisControl.Width = Val(BoxWidth.Text) fm1NewProps(ListControls.ListIndex + 1, fm1cdWidth) = fm1ThisControl.Width End Sub Private Sub CheckFontBold_Click() fm1ThisControl.Font.Bold = CheckFontBold.Value fm1NewProps(ListControls.ListIndex + 1, fm1cdFontBold) = fm1ThisControl.Font.Bold End Sub Private Sub CheckFontItalic_Click() fm1ThisControl.Font.Italic = CheckFontItalic.Value fm1NewProps(ListControls.ListIndex + 1, fm1cdFontItalic) = fm1ThisControl.Font.Italic End Sub Private Sub CheckFontUnderline_Click() fm1ThisControl.Font.Underline = CheckFontUnderline.Value fm1NewProps(ListControls.ListIndex + 1, fm1cdFontUnderline) = fm1ThisControl.Font.Underline End Sub Private Sub CheckMultiLine_Click() fm1ThisControl.MultiRow = CheckMultiLine.Value fm1NewProps(ListControls.ListIndex + 1, fm1cdMultiLine) = fm1ThisControl.MultiRow End Sub Private Sub ComboSelForm_Change() Dim fm1N As Integer ' Get the form and make the calling CommandButton invisible Set fm1ThisForm = UserForms(ComboSelForm.ListIndex) fm1ThisForm.Controls(fm1ThisForm.Controls.Count - 1).Visible = False ' Remove form selection combobox from view, and enable other controls ComboSelForm.Visible = False: LabelSelForm.Visible = False Label1.Visible = True: BoxLeft.Visible = True: SpinLeft.Visible = True: CommandCopyLeft.Visible = True Label2.Visible = True: BoxTop.Visible = True: SpinTop.Visible = True: CommandCopyTop.Visible = True Label3.Visible = True: BoxWidth.Visible = True: SpinWidth.Visible = True: CommandCopyWidth.Visible = True Label4.Visible = True: BoxHeight.Visible = True: SpinHeight.Visible = True: CommandCopyHeight.Visible = True CommandCode.Enabled = True ListControls.Enabled = True BoxSpinUnit.Enabled = True OptionVisible.Enabled = True: OptionDefault.Enabled = True MultiPage1.page2.Enabled = True: MultiPage1.page3.Enabled = True: MultiPage1.page4.Enabled = True ' Set up the property arrays fm1PropName = Array(".Left", ".Top", ".Width", ".Height", ".Font.Name", ".Font.Size", ".Font.Bold", ".Font.Italic", ".Font.Underline", ".ColumnWidths", ".TabFixedWidth", ".TabFixedHeight", ".MultiRow", ".TabOrientation", ".TabStyle", ".Zoom") ReDim fm1OldProps(1 To fm1ThisForm.Controls.Count, 1 To 16) fm1OldProps(UBound(fm1OldProps, 1), fm1cdWidth) = fm1ThisForm.Width fm1OldProps(UBound(fm1OldProps, 1), fm1cdHeight) = fm1ThisForm.Height fm1OldProps(UBound(fm1OldProps, 1), fm1cdZoom) = fm1ThisForm.Zoom For fm1N = 0 To fm1ThisForm.Controls.Count - 2 With fm1ThisForm.Controls(fm1N) ListControls.AddItem .Name fm1OldProps(fm1N + 1, fm1cdLeft) = .Left fm1OldProps(fm1N + 1, fm1cdTop) = .Top fm1OldProps(fm1N + 1, fm1cdWidth) = .Width fm1OldProps(fm1N + 1, fm1cdHeight) = .Height Select Case TypeName(fm1ThisForm.Controls(fm1N)) Case "TextBox", "Label", "Frame", "ListBox", "CheckBox", "OptionButton", "ToggleButton", "CommandButton", "MultiPage", "RefEdit", "TabStrip" fm1OldProps(fm1N + 1, fm1cdFontName) = .Font.Name fm1OldProps(fm1N + 1, fm1cdFontSize) = .Font.Size fm1OldProps(fm1N + 1, fm1cdFontBold) = .Font.Bold fm1OldProps(fm1N + 1, fm1cdFontItalic) = .Font.Italic fm1OldProps(fm1N + 1, fm1cdFontUnderline) = .Font.Underline End Select If TypeName(fm1ThisForm.Controls(fm1N)) = "ListBox" Or TypeName(fm1ThisForm.Controls(fm1N)) = "CombBox" Then fm1OldProps(fm1N + 1, fm1cdColWidths) = .ColumnWidths End If If TypeName(fm1ThisForm.Controls(fm1N)) = "TabStrip" Or TypeName(fm1ThisForm.Controls(fm1N)) = "MultiPage" Then fm1OldProps(fm1N + 1, fm1cdTabWidth) = .TabFixedWidth fm1OldProps(fm1N + 1, fm1cdTabHeight) = .TabFixedHeight fm1OldProps(fm1N + 1, fm1cdMultiLine) = .MultiRow fm1OldProps(fm1N + 1, fm1cdTabPos) = .TabOrientation fm1OldProps(fm1N + 1, fm1cdTabStyle) = .Style End If ListControls.List(ListControls.ListCount - 1, 1) = TypeName(fm1ThisForm.Controls(fm1N)) If fm1ThisForm.Controls(fm1N).Visible Then ListControls.List(ListControls.ListCount - 1, 2) = "True" Else ListControls.List(ListControls.ListCount - 1, 2) = "False" End If End With Next fm1N fm1NewProps = fm1OldProps BoxFormWidth = fm1ThisForm.Width BoxFormHeight = fm1ThisForm.Height BoxFormZoom = fm1ThisForm.Zoom fm1SpinUnit = 1 End Sub Private Sub ComboTabPosition_Change() fm1ThisControl.TabOrientation = ComboTabPosition.ListIndex fm1NewProps(ListControls.ListIndex + 1, fm1cdTabPos) = fm1ThisControl.TabOrientation End Sub Private Sub ComboTabStyle_Change() fm1ThisControl.Style = ComboTabStyle.ListIndex fm1NewProps(ListControls.ListIndex + 1, fm1cdTabStyle) = fm1ThisControl.Style End Sub Private Sub CommandCode_Click() Dim fm1NumRow As Integer, fm1N As Integer, fm1J As Integer ' Need to add double quotes around string contents For fm1N = 0 To fm1ThisForm.Controls.Count - 2 If fm1NewProps(fm1N + 1, fm1cdFontName) <> "" Then fm1OldProps(fm1N + 1, fm1cdFontName) = Chr(34) & fm1OldProps(fm1N + 1, fm1cdFontName) & Chr(34) fm1NewProps(fm1N + 1, fm1cdFontName) = Chr(34) & fm1NewProps(fm1N + 1, fm1cdFontName) & Chr(34) End If If fm1NewProps(fm1N + 1, fm1cdColWidths) <> "" Then fm1OldProps(fm1N + 1, fm1cdColWidths) = Chr(34) & fm1OldProps(fm1N + 1, fm1cdColWidths) & Chr(34) fm1NewProps(fm1N + 1, fm1cdColWidths) = Chr(34) & fm1NewProps(fm1N + 1, fm1cdColWidths) & Chr(34) End If Next fm1N ' Place the table and code on two new sheets Call VerifySheet("VBA_Form_Mod_Table") Call VerifySheet("VBA_Form_Mod_Code") With Sheets("VBA_Form_Mod_Table") .Activate ' DUmp property names and values For fm1N = 0 To UBound(fm1PropName) .Cells(1, fm1N + 2) = fm1PropName(fm1N) Next fm1N For fm1J = 0 To fm1ThisForm.Controls.Count - 2 .Cells(fm1J + 2, 1) = fm1ThisForm.Controls(fm1J).Name Next fm1J .Cells(fm1J + 2, 1) = fm1ThisForm.Name .Cells(fm1J + 4, 1) = "Note that " & fm1ThisForm.Name & " [last line of table] is the UserForm, not a control." .Cells(fm1J + 5, 1) = "Modified properties are highlighted, and VBA code for modifications is given on VBA_Form_Mod_Code" .Cells(2, 2).Resize(UBound(fm1NewProps, 1), UBound(fm1NewProps, 2)) = fm1NewProps .Range("1:65536").NumberFormat = "General" fm1NumRow = 1 ' Look for modified properties: highlight them, and add code to worksheet. For fm1J = 0 To fm1ThisForm.Controls.Count - 2 For fm1N = 0 To UBound(fm1PropName) If fm1NewProps(fm1J + 1, fm1N + 1) <> fm1OldProps(fm1J + 1, fm1N + 1) Then .Cells(fm1J + 2, fm1N + 2).Interior.ColorIndex = 27 Sheets("VBA_Form_Mod_Code").Cells(fm1NumRow, 1) = fm1ThisForm.Controls(fm1J).Name & fm1PropName(fm1N) & " = " & fm1NewProps(fm1J + 1, fm1N + 1) fm1NumRow = fm1NumRow + 1 End If Next fm1N Next fm1J ' Form.Width, .Height, .Zoom handled separately If fm1ThisForm.Zoom <> fm1OldProps(UBound(fm1OldProps, 1), fm1cdZoom) Then Sheets("VBA_Form_Mod_Code").Cells(fm1NumRow, 1) = fm1ThisForm.Name & ".Zoom = " & fm1ThisForm.Zoom .Cells(fm1J + 2, fm1cdZoom + 1).Interior.ColorIndex = 27 fm1NumRow = fm1NumRow + 1 End If If fm1ThisForm.Width <> fm1OldProps(UBound(fm1OldProps, 1), fm1cdWidth) Then Sheets("VBA_Form_Mod_Code").Cells(fm1NumRow, 1) = fm1ThisForm.Name & ".Width = " & fm1ThisForm.Width .Cells(fm1J + 2, fm1cdWidth + 1).Interior.ColorIndex = 27 fm1NumRow = fm1NumRow + 1 End If If fm1ThisForm.Height <> fm1OldProps(UBound(fm1OldProps, 1), fm1cdHeight) Then Sheets("VBA_Form_Mod_Code").Cells(fm1NumRow, 1) = fm1ThisForm.Name & ".Height = " & fm1ThisForm.Height .Cells(fm1J + 2, fm1cdHeight + 1).Interior.ColorIndex = 27 fm1NumRow = fm1NumRow + 1 End If Sheets("VBA_Form_Mod_Code").Cells(fm1NumRow + 2, 1) = "Table of modifications on VBA_Form_Mod_Table" End With ' Make calling CommandButton visible again fm1ThisForm.Controls(fm1ThisForm.Controls.Count - 1).Visible = True Set fm1ThisControl = Nothing Set fm1ThisForm = Nothing Unload Me End Sub Private Sub CommandCopyFont_Click() CommandPaste.Enabled = True CommandPaste.Caption = "Paste Font Info" fm1PasteProp = "Font" fm1PasteVal = ListControls.ListIndex End Sub Private Sub CommandCopyHeight_Click() CommandPaste.Enabled = True CommandPaste.Caption = "Paste " & BoxHeight.Text & " to Control.Height" fm1PasteProp = "Height" fm1PasteVal = BoxHeight.Text End Sub Private Sub CommandCopyLeft_Click() CommandPaste.Enabled = True CommandPaste.Caption = "Paste " & BoxLeft.Text & " to Control.Left" fm1PasteProp = "Left" fm1PasteVal = BoxLeft.Text End Sub Private Sub CommandCopyTop_Click() CommandPaste.Enabled = True CommandPaste.Caption = "Paste " & BoxTop.Text & " to Control.Top" fm1PasteProp = "Top" fm1PasteVal = BoxTop.Text End Sub Private Sub CommandCopyWidth_Click() CommandPaste.Enabled = True CommandPaste.Caption = "Paste " & BoxWidth.Text & " to Control.Width" fm1PasteProp = "Width" fm1PasteVal = BoxWidth.Text End Sub Private Sub CommandOK_Click() fm1ThisForm.Controls(fm1ThisForm.Controls.Count - 1).Visible = True Set fm1ThisForm = Nothing Set fm1ThisControl = Nothing Unload Me End Sub Private Sub CommandPaste_Click() If fm1PasteProp = "Left" Then BoxLeft.Text = fm1PasteVal ElseIf fm1PasteProp = "Top" Then BoxTop.Text = fm1PasteVal ElseIf fm1PasteProp = "Width" Then BoxWidth.Text = fm1PasteVal ElseIf fm1PasteProp = "Height" Then BoxHeight.Text = fm1PasteVal ElseIf fm1PasteProp = "Font" Then BoxFont.Text = fm1ThisControl.Parent.Item(fm1PasteVal).Font.Name BoxFontSize.Text = fm1ThisControl.Parent.Item(fm1PasteVal).Font.Size CheckFontBold.Value = fm1ThisControl.Parent.Item(fm1PasteVal).Font.Bold CheckFontItalic.Value = fm1ThisControl.Parent.Item(fm1PasteVal).Font.Italic CheckFontUnderline.Value = fm1ThisControl.Parent.Item(fm1PasteVal).Font.Underline End If End Sub Private Sub ListControls_Click() Set fm1ThisControl = fm1ThisForm.Controls(ListControls.ListIndex) BoxLeft.Text = fm1ThisControl.Left BoxTop.Text = fm1ThisControl.Top BoxWidth.Text = fm1ThisControl.Width BoxHeight.Text = fm1ThisControl.Height If fm1ThisControl.Visible And ListControls.List(ListControls.ListIndex, 2) = "False" Then OptionVisible.Value = True OptionDefault.Value = False Else OptionVisible.Value = False OptionDefault.Value = True End If Select Case TypeName(fm1ThisControl) Case "TextBox", "Label", "Frame", "ListBox", "CheckBox", "OptionButton", "ToggleButton", "CommandButton", "MultiPage", "RefEdit", "TabStrip" BoxFont.Enabled = True BoxFontSize.Enabled = True Label6.Enabled = True Label7.Enabled = True SpinFontSize.Enabled = True CheckFontBold.Enabled = True CheckFontItalic.Enabled = True CheckFontUnderline.Enabled = True CommandCopyFont.Enabled = True If fm1PasteProp <> "" Then CommandPaste.Enabled = True Else CommandPaste.Enabled = False End If BoxFont.Text = fm1ThisControl.Font.Name BoxFontSize.Text = fm1ThisControl.Font.Size If fm1ThisControl.Font.Bold Then CheckFontBold.Value = True Else CheckFontBold.Value = False End If If fm1ThisControl.Font.Italic Then CheckFontItalic.Value = True Else CheckFontItalic.Value = False End If If fm1ThisControl.Font.Underline Then CheckFontUnderline.Value = True Else CheckFontUnderline.Value = False End If Case "ScrollBar", "SpinButton", "Image" BoxFont.Enabled = False BoxFontSize.Enabled = False SpinFontSize.Enabled = False Label6.Enabled = False Label7.Enabled = False CheckFontBold.Enabled = False CheckFontItalic.Enabled = False CheckFontUnderline.Enabled = False CommandCopyFont.Enabled = False If fm1PasteProp <> "" And fm1PasteProp <> "Font" Then CommandPaste.Enabled = True Else CommandPaste.Enabled = False End If End Select If TypeName(fm1ThisControl) = "ListBox" Or TypeName(fm1ThisControl) = "ComboBox" Then LabelNumCols.Enabled = True BoxColWidth.Enabled = True Label9.Enabled = True LabelNumCols.Caption = "Number of Columns: " & fm1ThisControl.ColumnCount BoxColWidth.Text = fm1ThisControl.ColumnWidths Else LabelNumCols.Enabled = False BoxColWidth.Enabled = False Label9.Enabled = False End If If TypeName(fm1ThisControl) = "TabStrip" Or TypeName(fm1ThisControl) = "MultiPage" Then Label13.Enabled = True Label14.Enabled = True BoxTabWidth.Enabled = True SpinTabWidth.Enabled = True BoxTabHeight.Enabled = True SpinTabHeight.Enabled = True Label15.Enabled = True CheckMultiLine.Enabled = True ComboTabPosition.Enabled = True Label16.Enabled = True ComboTabStyle.Enabled = True BoxTabWidth.Text = fm1ThisControl.TabFixedWidth BoxTabHeight.Text = fm1ThisControl.TabFixedHeight CheckMultiLine.Value = fm1ThisControl.MultiRow ComboTabPosition.ListIndex = fm1ThisControl.TabOrientation ComboTabStyle.ListIndex = fm1ThisControl.Style Else Label13.Enabled = False Label14.Enabled = False BoxTabWidth.Enabled = False SpinTabWidth.Enabled = False BoxTabHeight.Enabled = False SpinTabHeight.Enabled = False Label15.Enabled = False CheckMultiLine.Enabled = False ComboTabPosition.Enabled = False Label16.Enabled = False ComboTabStyle.Enabled = False End If End Sub Function validate(fm1TestVal As Variant, fm1ControlName As String) As Boolean ' This performs basic TextBox validation. Valid values are displayed black, invalid red. If fm1TestVal = "." Then validate = True ElseIf IsNumeric(fm1TestVal) Then If Val(fm1TestVal) >= 0 Then validate = True Else validate = False End If Else validate = False End If If validate Then Me.Controls(fm1ControlName).ForeColor = 0 Else Me.Controls(fm1ControlName).ForeColor = 255 End If End Function Private Sub OptionDefault_Click() If ListControls.List(ListControls.ListIndex, 2) = "True" Then fm1ThisControl.Visible = True Else fm1ThisControl.Visible = False End If End Sub Private Sub OptionVisible_Click() fm1ThisControl.Visible = True End Sub Private Sub SpinFontSize_Change() BoxFontSize.Text = Val(BoxFontSize.Text) + SpinFontSize.Value SpinFontSize.Value = 0 End Sub Private Sub SpinFormHeight_Change() BoxFormHeight.Text = fm1ThisForm.Height + fm1SpinUnit * SpinFormHeight.Value SpinFormHeight.Value = 0 End Sub Private Sub SpinFormWidth_Change() BoxFormWidth.Text = fm1ThisForm.Width + fm1SpinUnit * SpinFormWidth.Value SpinFormWidth.Value = 0 End Sub Private Sub SpinFormZoom_Change() BoxFormZoom.Text = fm1ThisForm.Zoom + fm1SpinUnit * SpinFormZoom.Value SpinFormZoom.Value = 0 End Sub Private Sub SpinHeight_Change() BoxHeight.Text = fm1ThisControl.Height + fm1SpinUnit * SpinHeight.Value SpinHeight.Value = 0 End Sub Private Sub SpinLeft_Change() BoxLeft.Text = fm1ThisControl.Left + fm1SpinUnit * SpinLeft.Value SpinLeft.Value = 0 End Sub Private Sub SpinTabHeight_Change() BoxTabHeight.Text = fm1ThisControl.TabFixedHeight + fm1SpinUnit * SpinTabHeight.Value SpinTabHeight.Value = 0 End Sub Private Sub SpinTabWidth_Change() BoxTabWidth.Text = fm1ThisControl.TabFixedWidth + fm1SpinUnit * SpinTabWidth.Value SpinTabWidth.Value = 0 End Sub Private Sub SpinTop_Change() BoxTop.Text = fm1ThisControl.Top + fm1SpinUnit * SpinTop.Value SpinTop.Value = 0 End Sub Private Sub SpinWidth_Change() BoxWidth.Text = fm1ThisControl.Width + fm1SpinUnit * SpinWidth.Value SpinWidth.Value = 0 End Sub Private Sub UserForm_Initialize() Dim fm1N As Integer With ComboTabPosition .AddItem "Top" .AddItem "Bottom" .AddItem "Left" .AddItem "Right" End With With ComboTabStyle .AddItem "Tabs" .AddItem "Buttons" .AddItem "None" End With With ListControls Label17.Left = .Left Label18.Left = .Left + 110 Label19.Left = .Left + 200 End With ' Improve form legibility on Mac If InStr(Application.OperatingSystem, "Macintosh") Then Me.Zoom = 120 Me.Width = 1.2 * Me.Width Me.Height = 1.2 * Me.Height End If ' Display list of available forms With ComboSelForm For fm1N = 0 To UserForms.Count - 1 .AddItem UserForms(fm1N).Name Next fm1N If .ListCount = 1 Then .ListIndex = 0 End With End Sub Sub VerifySheet(fm1NewSheet As String) Dim fm1N As Integer, fm1CurrentSheet As String For fm1N = 1 To Worksheets.Count If Sheets(fm1N).Name = fm1NewSheet Then Sheets(fm1N).Range("1:65536").ClearContents Exit Sub End If Next fm1N fm1CurrentSheet = ActiveSheet.Name Worksheets.Add ActiveSheet.Name = fm1NewSheet Sheets(fm1CurrentSheet).Activate End Sub

How to use:

  1. Download sample file and expand it.
  2. Open Excel with the file that contains the form you wish to work on.
  3. Alt-F11 to open the VBE.
  4. (Menus) File -> Import... (choose mod_tool.frm from the downloaded files) Mac users see the following KB to import the file: <not yet approved>
  5. Double-click on your form (the one to be modified) in the Project window (left-side of screen).
  6. Using ToolBox, place one CommandButton on the form you wish to modify. (This must be the final control added to the form.)
  7. Double-click on the CommandButton.
  8. At the insertion point in the code window, enter: ModTool.Show
  9. Close VBE and return to Excel.
  10. Note: Due to a lack of Mac users, this code is being published without the usual rigorous testing. If you should encounter any bugs, please contact the author by PM or post a question in the Mac forum
  11. mdmackillop VP-Knowledge Base
 

Test the code:

  1. Run your code as usual.
  2. Click the new button on your form to start the form modifier.
  3. Click 'Generate Code' to add sheets to your workbook containing the modification results.
  4. When you are done, you can remove the modifier tool by: a) pressing Alt-F11 to go to the VBE; b) In the Projext Window, right-click on ModTool (under Forms), Remove; c) Remove the CommandButton and its associated code from your own form.
 

Sample File:

mod_tool component.zip 8.72KB 

Approved by mdmackillop


This entry has been viewed 305 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express