Creating a Spin Box Control

Many Windows dialog boxes offer a spin box control that lets you change a number without typing. Oddly enough, there is no spin box control in the form's Design Toolbox to let you create such a control on your Access forms.

If you want to add a spin box control to an Access form, you have to fudge it. Writing the code for the spin buttons is easy; creating the little buttons is the real challenge.

I've used numerous techniques to create the spin buttons. I've imported ActiveX controls, used command buttons with a special character like an up or down arrow, and even used transparent-background GIFs to put a tiny arrow on each command button. Because the spin buttons are so tiny, though, getting the command button to look right is difficult.

I finally just gave in and drew each button as a tiny graphic image. (It really doesn't matter whether you use a command button or a picture for the spin button because buttons and pictures both have On Click events to which you can tie code.) Figure 9-8 shows buttons that I drew for this example magnified 800 percent in Paint Shop Pro. The lower half of that same picture shows the buttons in place on a form. To get the buttons onto the form, I just used the standard InsertOPicture commands on the Access menu bar in forms Design.

Spin Box Design
Spin buttons

I'll post some spin buttons at www.coolnerds.com/vba that you can download and use on your own forms.

Regardless of whether you use command buttons to pictures to get spin buttons onto a form, getting them to work is the same. You can name each button as you would any other control (via the Name property on the All tab of the Properties sheet). I named my two picture buttons SpinUpBttn and SpinDownBttn.

After you have the controls on the form in Design view, click the Spin Up button control, click Events in the Properties sheet, click the On Click event, click the Code button, and then choose Code Builder. The VBA editor opens with the cursor already in a procedure named SpinUpBttn_Click() (assuming that you named your spin up button SpinUp). In my example, I want each click of the Spin Up button to increase the value in the LabelsToSkip control by 1.

Use an If...Then...End If statement to put an upper limit on how high the value can go. I chose 80 as an upper limit (because I doubt there are many label sheets that offer more than 80 labels per page), but you can set your upper limit to any value you want. Following is the code to increase the value in the

LabelsToSkip control each time a user clicks the form's SpinUpBttn control:

Private Sub SpinUpBttn_Click()

'Increase LabelsToSkip by 1 to a maximum of 80. If Me!LabelsToSkip.Value < 80 Then

Me!LabelsToSkip.value = Me.LabelsToSkip.Value+1 End If End Sub

After writing the code for the SpinUpBttn and returning to your form in forms Design, click the SpinDownBttn control on your form. Again, get to that control's On Click event in the Properties sheet and write a routine like the one that follows. In that example, I put a lower limit of 0 (zero) on the value in the

LabelsToSkip control:

Private Sub SpinDownBttn_Click()

'Decrease LabelsToSkip by 1 to a minimum of 0. If Me!LabelsToSkip.Value > 0 Then

Me!LabelsToSkip.value = Me.LabelsToSkip.Value-1 End If End Sub

The following code shows both procedures in place in the class module for my Fancy SkipLabels dialog box example. Again, the biggest trick to getting spin buttons on a form is getting buttons that are small enough to fit next to the control. But after you have a command button or picture in place, you can program its On Click event to increase or decrease the value in a numeric field by one with each click.

Private Sub SpinDownBttn_Click(

'Decrease LabelsToSkip by 1

to a minimum of 0.

'If Me!LabelsToSkip.Value >

0 Then

Me!LabelsToSkip.Value =

Me.LabelsToSkipValue - 1

End If

End Sub

Private Sub SpinUpBttn_Click()

'Increase LabelsToSkip by 1 to a maximum of

80.

If Me!LabelsToSkip.Value < 80 Then

Me!LabelsToSkip.Value = Me.LabelsToSkip

.Value + 1

End If

End Sub

0 -2

Responses

  • Jukka Peltosaari
    How to get picture form access vba?
    7 years ago

Post a comment