Excel Form - Displaying 2 Digits (Zero fill) and Reacting to Forward/Back

[ellensatter]

I teach Java programming in a High School and I was given a deli type number dispensing machine that I want to use when my students are coding so they can queue for help with their programs with a minimum of fuss.


I want a Now Serving app I can project on my Smart Board to go along with the machine. A real program from the manufacturer is too spendy for me, but I thought - how hard could it be to write one? (Famous last words, right?) After all, I'm a former mainframe programmer and pretty good with Excel already.


I've gotten pretty far on my own. I have a nice form displaying the number in a digital font and the forward and back buttons advance and go back correctly (from 99 to 0 going up and from 0 to 99 going back). But I'm struggling with two things:

1) I'd like 0 through 9 to display as 00-09
2) I have a clicker that I use for power points, and I would like the forward and back button on my clicker to advance and go back like the buttons do.
1336613367

[Paul_Hossler]

Some general thoughts

1. In TextBox1_Change, use something like



Application.EnableEvents = False
TextBox1.Text = Format (Clng(TextBox1.Text), "00")
Application.EnableEvents = True


to avoid the handler calling itself, and Text is probably not Dim-ed

2. Is there Option Explicit at the start of the module? I always like to use it


3. Possible re-design / re-Structuring suggestions

a. Dim a Public NowServing Long
b. In the Prev/Next command button event handler, add or subtract 1 to NowServing
c. In those handlers, update the TextBox



TextBox1.Text = Format (NowServing, "00")



4. Why are you updating A2? You can do everythin in your userform



PS. If you use the [#] icon you get the [ CODE ] [ / CODE] pair so you can paste your code inbetween (no more jpg's)

[SamT]

I took a different approach than Paul. I imagined a standalone app that could be used in Excel or PowerPoint. You can drag a UserForm from one VBA to another, or Export it from Excel and Import it into PowerPoint or other VBA and MS Forms Enabled Application.

I changed the names for easier reading comprehension. The UserForm Modules is named "frmNowServing," hinting that when Instantiated the Object should be named "NowServing." the TextBox is "Display" and the CommandButtons are "cbutNext" and "cbutPrevious."

I gave the App one Property, (Value, a Long,) and two Methods, (RollUp & RollDown.) These can be accessed like any other VBA Object's Properties and Methods. Assuming the Instantiated Objects Name is NowServing...


X = NowServing.Value
NowServing.RollUp
Y = NowServing.Value


With nowServing
X = .Value
.RollUp
Y = .Value
End With
In both cases above Y = X + 1 (Except if X = 99 :)


Option Explicit

Private Sub cbutNext_Click()
'Your "CommandButton1"

'On click, runs sub RollUp
RollUp
End Sub


Private Sub cbutPrevious_Click()
'Your "CommandButton2"

RollDown
End Sub


Private Sub Display_Change()
'My "Display" is your "TextBox1"

'Insures doubledigit numbers

'Prevent Flickering
Application.ScreenUpdating = False

If Len(Display) = 1 Then _
Display = "0" & Display 'The Value of a TextBox is a String

Application.ScreenUpdating = True
End Sub

Public Sub RollUp()

If Display = "99" Then
Display = "00"
Else
Display = CStr(CLng(Display) + 1) 'From String to Long + 1 to String again
End If

End Sub


Public Sub RollDown()

If Display = "00" Then
Display = "99"
Else
Display = CStr(CLng(Display) - 1)
End If

End Sub


Property Get Value()
'Returns the numerical, (Long,) value of the TextBox "Display."
'ie a number from 0 to 99

Value = CLng(Display)
End Property




Without knowing what signals the PP-Clicker is emulating, I can't help. It might be mouse Right and left clicks, Mouse Up & Down???. When you find out, have those Event Subs, (of the Form?) call RollUp and RollDown.

[Paul_Hossler]

Something like this

BTW, I think you really want to use the Label control, and not the TextBox




Option Explicit

Dim iNowServing As Long

Private Sub cbExit_Click()
Me.Hide
Unload Me
End Sub

Private Sub labNext_Click()
iNowServing = iNowServing + 1
If iNowServing = 100 Then iNowServing = 1
Me.labNowServing.Caption = Format(iNowServing, "00")
End Sub

Private Sub labPrev_Click()
iNowServing = iNowServing - 1
If iNowServing = 0 Then iNowServing = 99
Me.labNowServing.Caption = Format(iNowServing, "00")
End Sub

Private Sub UserForm_Initialize()
iNowServing = 1
Me.labNowServing.Caption = Format(iNowServing, "00")
End Sub



Here's a little sample to give you some ideas possibly

Needs polish, and I used the only LCD font I had so I don't actually know how it will show up