View Full Version : [VBA - UserForms] Adding
chrischris90
08-19-2010, 06:25 AM
Hey!
I need to add a record of items (as is done with a listbox) but I need to add OptionButton items to the form for every item that is listed in the listbox, how will I add the OptionButtons to the form for every item after the macro (userform) has been initialised, (edit the height + add the things).
Regards,
Chris
Bob Phillips
08-19-2010, 06:40 AM
'----------------------------------------------------------------
Sub ListOptions()
'----------------------------------------------------------------
Const nPerColumn As Long = 35 'number of items per column
Const nWidth As Long = 11 'width of each letter
Const nHeight As Long = 18 'height of each row
Const sID As String = "___SheetOptions" 'name of dialog sheet
Const kCaption As String = " Select Option"
'dialog caption
Dim i As Long
Dim LastRow As Long
Dim TopPos As Long
Dim iBooks As Long
Dim cCols As Long
Dim cLetters As Long
Dim cMaxLetters As Long
Dim iLeft As Long
Dim thisDlg As DialogSheet
Dim cb As OptionButton
Application.ScreenUpdating = False
If ActiveWorkbook.ProtectStructure Then
MsgBox "Workbook is protected.", vbCritical
Exit Sub
End If
On Error Resume Next
Application.DisplayAlerts = False
ActiveWorkbook.DialogSheets(sID).Delete
Application.DisplayAlerts = True
On Error GoTo 0
Set thisDlg = ActiveWorkbook.DialogSheets.Add
With thisDlg
.Name = sID
.Visible = xlSheetHidden
'sets variables for positioning on dialog
iBooks = 0
cCols = 0
cMaxLetters = 0
iLeft = 78
TopPos = 40
For i = 1 To Range("A1").End(xlDown).Row
If i Mod nPerColumn = 1 Then
cCols = cCols + 1
TopPos = 40
iLeft = iLeft + (cMaxLetters * nWidth)
cMaxLetters = 0
End If
cLetters = Len(Cells(i, "A").Value)
If cLetters > cMaxLetters Then
cMaxLetters = cLetters
End If
iBooks = iBooks + 1
.OptionButtons.Add iLeft, TopPos, cLetters * nWidth, 16.5
.OptionButtons(iBooks).Text = Cells(i, "A").Value
TopPos = TopPos + 13
Next i
.Buttons.Left = iLeft + (cMaxLetters * nWidth) + 24
With .DialogFrame
.Height = Application.Max(68, _
Application.Min(iBooks, nPerColumn) * nHeight + 10)
.Width = iLeft + (cMaxLetters * nWidth) + 24
.Caption = kCaption
End With
.Buttons("Button 2").BringToFront
.Buttons("Button 3").BringToFront
Application.ScreenUpdating = True
If .Show Then
For Each cb In thisDlg.OptionButtons
If cb.Value = xlOn Then
Call MyAction(cb.Caption)
Exit For
End If
Next cb
Else
MsgBox "Nothing selected"
End If
Application.DisplayAlerts = False
.Delete
End With
End Sub
Private Sub MyAction(Arg As String)
MsgBox Arg
End Sub
chrischris90
08-19-2010, 06:41 AM
Thanks, I'll take a look at that :D
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.