Counter in VBA (Word)

Topic

New Reply

Hi all – I need some advice. I need to create a counter for the staff on the telephone at my work. Basically I want to create a little window that has three fields on it:
1. A lable called callsNum. This field displays the number of phone calls so far for the day.
2. A command button called CommandButton1. The user clicks on this button and it increases the callsNum field by 1.
3. A command button called CommandButton2. The user clicks on this button to set the callsNum field to 0.
I have created the fields and put them on a blank form called UserForm. The code for CommandButton2 has been easy enough for me but I am having trouble getting the code for CommandButton1.
Anyone got any examples I could use?
Thanks heaps in advance.

Bill

Reply | Quote

Replies

Yeah, most programming languages don’t like you to try to do math operations on text labels. So you first need to turn the text label into a number. Look up the CInt function in the online help and see if it works for you. You could use it more or less as follows:

Label1.Caption = CInt(Label1.Caption) + 1

Or, in English, take the text in Label1 (its Caption property), turn it into an integer, add 1, then stick it back into Label1.

Does that do the trick?

I actually think it might look better if you use a textbox but make it not user editable. If you do that, the way you get at the contents would be something like .Value or .Text rather than .Caption.

Reply | Quote

OK, I got it I think. One question is how do I make the text box not user editable? Is it anything to do with the Enabled property? Do I set it to false?
Thanks for your help.

Bill

Reply | Quote

Hi again. Just when I thought I had it, I discover I haven’t. When I adapt the code you sent me as follows:
Private Sub CommandButton1_Click()
txtCallNum.Value = CInt(txtCallNum.Value) + 1
End Sub

I get a Run Time Error 13, Type Mis Match error? Any idea where my problem is. I have tried .Value and .Text with the same result.
Thanks heaps.

Bill

Reply | Quote

This will occur if the value/text of txtCallNum is not numeric, for instance if it is empty. You could set the text or value to 0 or another initial value in the Visual Basic Editor, or you can add a check:

Private Sub CommandButton1_Click()
If IsNumeric(txtCallNum.Value) Then
txtCallNum.Value = CInt(txtCallNum.Value) + 1
Else
txtCallNum.Value = 1 ‘ or whatever value you prefer
End If
End Sub

Reply | Quote

Hans – Bingo, got it! Thanks heaps for your help. I learn a little bit more each day. Thanks so much.

Bill

Reply | Quote

Hi again – Why is it then that the following code attached to a seperate command button won’t reset the value of the field back to 0?

Private Sub CommandButton3_Click()
MsgBox “IMPORTANT! – If you click OK your call count will return to O.”, _
vbOKCancel + vbExclamation + vbDefaultButton3
Set txtCallNum.Value = 0

End Sub

What have I missed this time?
Thanks heaps.

Bill

Reply | Quote

> Set txtCallNum.Value = 0

You only use Set to create object references. All other assignments, you leave it out. Does that fix it?

Reply | Quote

Hi again. Not really. I can leave it out but I am left with nothing for the code to do. As you can see.
Private Sub CommandButton3_Click()
MsgBox “IMPORTANT! – If you click OK your call count will return to O.”, _
vbOKCancel + vbExclamation + vbDefaultButton3
End Sub

What I am trying to get the code to do is for the user to click on CommandButton3 and the field txtCallsNum is reset to 0. But I want a message box to pop up warning the user that this is going to happen if they click OK, and the message box to go away and the number in txtCallsNum to stay the same if they click Cancel.

Thanks for your patient help.

Bill

Reply | Quote

Bill,

MsgBox is a function. It takes several arguments, the second of which determines whcih buttons will be displayed. It returns a value that corresponds to the button clicked by the user.

If you call MsgBox as a procedure:

MsgBox "Are you sure?", vbYesNo

the return value will vanish into blue air. There is no way for you as programmer to know which button the user clicked.

If you want to do different things depending on the button clicked by the user, you must call MsgBox as a function and inspect the result:

If MsgBox("Are you sure?", vbYesNo) = vbYes Then
  ' code to execute if user clicked Yes goes here
Else
  ' code to execute if user clicked No goes here
End If

The vbDefaultButton bit determines which button will be the default button (that will react to Enter). If you display OK and Cancel buttons, it doesn’t make sense to use vbDefaultButton3, because there are only two buttons.

Here is what your code could look like:

Private Sub CommandButton3_Click()
  If MsgBox(Do you want to reset the call count to zero?", _
      vbYesNo + vbDefaultButton2 + vbQuestion) = vbYes Then
    txtCallNum.Value = 0
  End If
End Sub

HTH

Reply | Quote

Hans – Sorry to be a pain but I copied your code with only slight modification (you had left out a “) and I get a Compile Error – End If without block If error. The code I am using is:

Private Sub CommandButton3_Click()
If MsgBox(“Do you want to reset the call count to zero?”, _
vbYesNo + vbDefaultButton2 + vbQuestion) = vbYes Then _
txtCallNum.Value = 0
End If

End Sub

Can you see where the problem is? I can’t.

Thanks heaps.

Bill

Reply | Quote

Sorry about the missing quote – it was air code.

You have put an underscore after Then that should be removed.

Reply | Quote

Thanks Hans. I owe you a beer! Somehow I don’t think you have heard the last of me. Now I have to work out how to program a button to send an e-mail to a predetermined address with the current displayed number in the message.
Thanks again.

Bill

Reply | Quote

(Edited by HansV on 21-Mar-03 13:34. Text removed, since it was completely off the mark. Sorry!)

Reply | Quote

Hans, that is amazing. I have adapted your code but I get a Compile Error – Viariable not defined error. I have adapted your code to:
Private Sub CommandButton2_Click()
Dim strSubject As String
Dim strMessage As String
Dim strTo As String
strSubject = “The amount of calls for Henry Ni today is – ”
strMessage = “The call number ” & Me.txtCallNum & ” has been assigned.”
strTo = “jnorthby@ricct.com”
DoCmd.SendObject To:=strTo, Subject:=strSubject, _
MessageText:=strMessage, EditMessage:=False
End Sub

The error would seem to be in the line:
DoCmd.SendObject To:=strTo, Subject:=strSubject, _
MessageText:=strMessage, EditMessage:=False

Has this error got anything to do with the fact that I have an Option Explicit statement at the very top of my code window?
Thanks heaps.

Bill

Reply | Quote

There is no DoCmd or SendObject in Word’s object model. I think the sample code might have been from a Microsoft Access project.

If you search the board for posts about automating e-mail, you will find numerous samples that could help point the way, particularly if your users use Outlook.

Reply | Quote

Jefferson,

This is my fault. I posted some code while on automatic Access pilot. I have removed the code from my reply and sent an apology to Bill, which he gracefully accepted.

Reply | Quote

As a separate issue, if you want to give the user a choice in that message box, you need to phrase it like this (actually, there are other ways, but this is the most natural):

If MsgBox(“My question”, vbYesNo + vbDefaultButton2) = vbYes Then
‘do stuff
End If

Reply | Quote