Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Counter in VBA (Word)

    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

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Counter in VBA (Word)

    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.

  3. #3
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counter in VBA (Word)

    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

  4. #4
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counter in VBA (Word)

    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

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Counter in VBA (Word)

    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

  6. #6
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counter in VBA (Word)

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

    Bill

  7. #7
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counter in VBA (Word)

    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

  8. #8
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Counter in VBA (Word)

    > Set txtCallNum.Value = 0

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

  9. #9
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Counter in VBA (Word)

    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

  10. #10
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counter in VBA (Word)

    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

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Counter in VBA (Word)

    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:<pre>MsgBox "Are you sure?", vbYesNo</pre>

    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:<pre>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</pre>

    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:<pre>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</pre>

    HTH

  12. #12
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counter in VBA (Word)

    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

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Counter in VBA (Word)

    Sorry about the missing quote - it was air code.

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

  14. #14
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counter in VBA (Word)

    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

  15. #15
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counter in VBA (Word)

    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

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •