Add scroll bar using VBA

  • Thread starter Thread starter Michael Beckinsale
  • Start date Start date
M

Michael Beckinsale

Hi All,

Below is the code l have to create a custom dialog box on the fly. It works
fine in most instances but in a few cases the number of check boxes created
causes the form to be too long and the bottom of the list is not visible.

To overcome the problem l would like to add a vertical scrollbar to the
dialog box.

Can anybody tell me how to do it ?

It will need to work in Excel 97

All contibutions gratefully received

Sub SelectUINS()
Dim i As Integer
Dim TopPos As Integer
Dim SheetCount As Integer
Dim PrintDlg As DialogSheet
Dim CurrentSheet As Worksheet
Dim cb As CheckBox
Dim Startsheet
Dim cc As Integer
Dim Qbox
Dim Qbox1

Sheets("Control").Select
Application.ScreenUpdating = False

Qbox = MsgBox("Is this the 1st time this workbook has been used ?",
vbYesNo, "IMPORTANT")

If Qbox = vbNo Then
Application.Calculate
Call Protect
End
Else
Qbox1 = MsgBox("Please make the selections on the following screen
CAREFULLY." & _
vbCrLf & vbCrLf & _
"The selections you make will form the basis for the whole financial
year. If you " & _
"change your mind during the year you may but you will have to start
with a clean version " & _
"of this workbook and MANUALLY RE-INPUT the forecast figures",
vbCritical + vbOKOnly, "Very Important")

Set Startsheet = ActiveSheet

' Check for protected workbook
If ActiveWorkbook.ProtectStructure Then
MsgBox "Workbook is protected.", vbCritical
Exit Sub
End If

' Add a temporary dialog sheet
Set CurrentSheet = ActiveSheet
Set PrintDlg = ActiveWorkbook.DialogSheets.Add
SheetCount = 0

' Add the checkboxes
TopPos = 40
Sheets("Vision Extract").Select
Range("Y2").Select
cc = ActiveCell.CurrentRegion.count
For i = 1 To cc
SheetCount = SheetCount + 1
PrintDlg.CheckBoxes.Add 78, TopPos, 200, 16.5
PrintDlg.CheckBoxes(SheetCount).Text = _
ActiveCell.Value
TopPos = TopPos + 13
ActiveCell.Offset(1, 0).Select
Next i

' Move the OK and Cancel buttons
PrintDlg.Buttons.Left = 275

' Set dialog height, width, and caption
With PrintDlg.DialogFrame
.Height = Application.Max _
(68, PrintDlg.DialogFrame.Top + TopPos - 34)
.Width = 275
.Caption = "Select UIN's to be forecast THIS YEAR"
End With

' Change tab order of OK and Cancel buttons
' so the 1st option button will have the focus
PrintDlg.Buttons("Button 2").BringToFront
PrintDlg.Buttons("Button 3").BringToFront

' Display the dialog box
CurrentSheet.Activate
Application.ScreenUpdating = True
If SheetCount <> 0 Then
If PrintDlg.Show Then
If vbCancel = True Then
' Delete temporary dialog sheet (without a
warning)
CurrentSheet.Activate
Application.DisplayAlerts = False
PrintDlg.Delete
Exit Sub
Else
Application.StatusBar = ("Please wait. Generating
OCS Reports for each UIN..................................................")
Application.ScreenUpdating = False
For Each cb In PrintDlg.CheckBoxes
If cb.Value = xlOn Then
Sheets("OCS Template").Select
Sheets("OCS Template").Copy
Before:=Sheets("End")
Sheets("OCS Template (2)").Select
Sheets("OCS Template (2)").Name =
Left(cb.Caption, 6)
Range("C2").Select
ActiveCell.Value = Left(cb.Caption,
6)
Range("C9").Select
End If
Next cb
End If
Else
' Reactivate original sheet
Startsheet.Activate
Range("A1").Select
End If

End If
End If

' Delete temporary dialog sheet (without a warning)
Application.DisplayAlerts = False
PrintDlg.Delete


'Calculate and clean up
Sheets("Control").Select
Application.StatusBar = ("Please wait. Calculating workbook
....................................")
Application.Calculate
Application.StatusBar = ("Please wait. Creating the 'BLB Cost Allocation
Factors' workbook.....................")
Application.StatusBar = False
Application.CutCopyMode = False
 
Jim,

Thanks for your quick response but the solution you offered doesn't appear
to work.

I think the problem may be that the form created "on the fly" is actually a
dialog sheet.

I have been able to add the scroll bar using,

PrintDlg.ScrollBars.Add

however the scroll bar doesn't actually do anything !

Any ideas ?

Regards
 
Michael

Sorry about that. I really don't know too much about DialogSheets. I misunderstood what you were asking and solved the problem if you were dealing with VBA Forms

Sorry about that

You may be better off changing this to a VBA Form. You can add controls via VBA with
frmTemp.Controls.Add "Forms.CheckBox.1", "Checkbox1", Tru
frmTemp.Controls("Checkbox1").Caption = "Test

Then you can use the frmTemp.ScrollBars = frmScrollBarsVertical etc

I don't imagine you want to recreate all the work you have done so far, so I'm pretty much of no help

Jim
 
Back
Top