Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-13-2018, 10:41 AM
ElCoyote ElCoyote is offline 10 Second Timer for Calling Bingo Windows 7 64bit 10 Second Timer for Calling Bingo Office 2010 64bit
Novice
10 Second Timer for Calling Bingo
 
Join Date: Mar 2017
Posts: 6
ElCoyote is on a distinguished road
Default 10 Second Timer for Calling Bingo

Already have a spreadsheet ("Sheet1") that will display the bingo numbers at random when a button is clicked. There are two buttons ("Next Number" to display the next number and "New Game" to clear the board) that work just fine. I have tried to add a box that would "countdown from 10 seconds." Would like to create such a box that would (1)- cause no error when it get to "zero" and (2) would restart the "countdown" when the next number is displayed. How hard would it be to make this into an "automatic" program? Know there are commercial programs out there - but they have too many bells and info displayed. Any help would be appreciated!
Here are the two button marcos.

Private Sub btnDraw_Click()

Dim linenumber As Integer, numpick As Integer
Dim bingonum As String

linenumber = Range("E1").Value
numpick = Range("B" & linenumber).Value
Range("B" & linenumber).ClearContents

If numpick > 60 Then
bingonum = "O " & numpick
ElseIf numpick > 45 Then
bingonum = "G " & numpick
ElseIf numpick > 30 Then
bingonum = "N " & numpick
ElseIf numpick > 15 Then
bingonum = "I " & numpick
Else
bingonum = "B " & numpick
End If



Range("M8").Value = bingonum

End Sub


Private Sub btnReset_Click()

Dim linenumber As Integer
linenumber = 1

Do

Range("B" & linenumber).Value = Range("A" & linenumber).Value

linenumber = linenumber + 1
Loop Until Range("A" & linenumber).Value = ""

Range("M8").ClearContents

End Sub
Reply With Quote
  #2  
Old 09-13-2018, 09:08 PM
Logit Logit is offline 10 Second Timer for Calling Bingo Windows 10 10 Second Timer for Calling Bingo Office 2007
Expert
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

.
Not certain how your code works. Can you post a copy of your workbook ?
Reply With Quote
  #3  
Old 09-14-2018, 12:02 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline 10 Second Timer for Calling Bingo Windows 7 64bit 10 Second Timer for Calling Bingo Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

Hi and welcome
in the future please select your code and press the #button while editing so as to wrap your code with tags. Thx
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #4  
Old 09-14-2018, 06:07 PM
ElCoyote ElCoyote is offline 10 Second Timer for Calling Bingo Windows 7 64bit 10 Second Timer for Calling Bingo Office 2010 64bit
Novice
10 Second Timer for Calling Bingo
 
Join Date: Mar 2017
Posts: 6
ElCoyote is on a distinguished road
Default 10 Second Timer for Calling Bingo Spreadsheet

Having never done this before, am trying to attach my spreadsheet as required.
If this doesn't come thru, pls provide instruction on how to "upload" a file.

Thxs
Attached Files
File Type: xlsm EXCEL BINGO PROGRAM Sep 5 20181.xlsm (29.0 KB, 11 views)
Reply With Quote
  #5  
Old 09-15-2018, 05:25 PM
Logit Logit is offline 10 Second Timer for Calling Bingo Windows 10 10 Second Timer for Calling Bingo Office 2007
Expert
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

.

Downloads at bottom ...

Timer code :


Code:
Option Explicit

Sub BtnRaz_Click()
    mTimer3.TimerOff
    LblTemps.Caption = "00:00:10"

    With TButton1
        .Caption = "Start"
        .ForeColor = &H8000&
        .Value = False
    End With
End Sub

Sub TimerStart()
    With TButton1
        If .Value = True Then
            .Caption = "Stop"
            .ForeColor = &H80&
            mTimer3.TimerOn 1000
        Else
            .Caption = "Start"
            .ForeColor = &H8000&
            mTimer3.TimerOff
        End If
    End With
End Sub


Private Sub TButton1_Click()
    TimerStart
End Sub

Private Sub UserForm_Initialize()
Application.WindowState = xlMinimized
    With TButton1
        .Caption = "Start"
        .ForeColor = &H8000&
        .Value = False
    End With
    
 Const C_VBA6_USERFORM_CLASSNAME = "ThunderDFrame"
    
    Dim ret As Long
    Dim formHWnd As Long
    
    'Get window handle of the userform
    
    formHWnd = FindWindow(C_VBA6_USERFORM_CLASSNAME, Me.Caption)
    If formHWnd = 0 Then
        Debug.Print Err.LastDllError
    End If

    'Set userform window to 'always on top'
    
    ret = SetWindowPos(formHWnd, HWND_TOPMOST, 0, 0, 0, 0, SWP_NOMOVE Or SWP_NOSIZE)
    If ret = 0 Then
        Debug.Print Err.LastDllError
    End If
End Sub

Private Sub UserForm_Terminate()
Application.WindowState = xlNormal
    mTimer3.TimerOff
    Unload Me
End Sub


Attached Images
File Type: jpg Timer.jpg (132.0 KB, 26 views)
Attached Files
File Type: xlsm EXCEL BINGO PROGRAM Sep 5 20181.xlsm (26.0 KB, 9 views)
File Type: xlsm Ten Sec Countdown In Form.xlsm (26.6 KB, 12 views)
Reply With Quote
  #6  
Old 09-15-2018, 07:33 PM
ElCoyote ElCoyote is offline 10 Second Timer for Calling Bingo Windows 7 64bit 10 Second Timer for Calling Bingo Office 2010 64bit
Novice
10 Second Timer for Calling Bingo
 
Join Date: Mar 2017
Posts: 6
ElCoyote is on a distinguished road
Default

Thxs Logit.....For all your help/support, but I guess I didn't explain what I needed! When the spreadsheet is loaded there's the Bingo Calling board with a box that displays "00:10" second. When the "NEXT NUMBER" button is clicked on - a bingo number would appear and the countdown would begin. When the countdown get to zero - it stops. When the "NEXT NUMBER" is pressed again - another bingo number would appear and "00:10" would also appear and the countdown would begin again. Somehow, the countdown would be connected to the "NEXT NUMBER" button.
Reply With Quote
  #7  
Old 09-16-2018, 05:43 AM
NoSparks NoSparks is offline 10 Second Timer for Calling Bingo Windows 7 64bit 10 Second Timer for Calling Bingo Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

Perhaps a simpler approach will suffice


@ Logit
OP has 64bit Excel
https://docs.microsoft.com/en-us/pre...31(v=office.14)
https://www.jkp-ads.com/articles/apideclarations.asp
Attached Files
File Type: xlsm EXCEL BINGO PROGRAM Sep 5 2018_v2.xlsm (30.6 KB, 11 views)
Reply With Quote
  #8  
Old 09-16-2018, 09:40 AM
ElCoyote ElCoyote is offline 10 Second Timer for Calling Bingo Windows 7 64bit 10 Second Timer for Calling Bingo Office 2010 64bit
Novice
10 Second Timer for Calling Bingo
 
Join Date: Mar 2017
Posts: 6
ElCoyote is on a distinguished road
Default

Hello NoSparks........thxs for you response and I see you added 1.6 kb to my original spreadsheet. However, I see nothing happening! What, where and/or how should something happen? I hoping for a "box" beneath the "Next Number" box that displays the "countdown timer."
Reply With Quote
  #9  
Old 09-16-2018, 10:39 AM
NoSparks NoSparks is offline 10 Second Timer for Calling Bingo Windows 7 64bit 10 Second Timer for Calling Bingo Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

Why don't you post your workbook that has the box and code that causes an error when it gets to "zero"
instead of a workbook that doesn't contain the things you're requesting assistance with ?


Then we would all be working on the same thing.
Know what the box is, where the box is and can figure out why it errors when getting to zero.
Reply With Quote
  #10  
Old 09-16-2018, 12:25 PM
ElCoyote ElCoyote is offline 10 Second Timer for Calling Bingo Windows 7 64bit 10 Second Timer for Calling Bingo Office 2010 64bit
Novice
10 Second Timer for Calling Bingo
 
Join Date: Mar 2017
Posts: 6
ElCoyote is on a distinguished road
Default

Sorry NoSparks......guess I'm still not clear on "My Help Needed!" I didn't save any worksheet where I tried to create a "countdown timer!" None of them worked, some even locked up my computer. "LOGIT's" and "tutorials on YouTube and/or VBABooks" shows how to create "countdown timers" with using BUTTONS. What I'm hoping for - a marco/program that would add a "countdown timer box" beneath the "NEXT NUMBER" that would start each time the "NEXT NUMBER" is clicked. I've seen where an error will occur if the timer reach "Zero," that's why I wanted the "stop" prompt. If I'm out-of-line with this type request.....would someone please tell me. I'm 79 and not getting any younger and trying to create this board for a Senior Center here in Maryland. Thxs.
Reply With Quote
  #11  
Old 09-16-2018, 12:58 PM
Logit Logit is offline 10 Second Timer for Calling Bingo Windows 10 10 Second Timer for Calling Bingo Office 2007
Expert
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

.
This version should answer all your questions :

Code:
Option Explicit

Sub starttimer()
On Error Resume Next
    Application.OnTime Now + TimeValue("00:00:01"), "nexttick"
End Sub

Sub nexttick()
On Error Resume Next
    If Sheet1.Range("S9") = 0 Then
        Sheet1.Range("S9").Value = "0:0:10"
        Exit Sub
    End If
    Sheet1.Range("S9").Value = Sheet1.Range("S9").Value - TimeValue("00:00:01")
    starttimer
End Sub

Sub resettimer()
    Sheet1.Range("S9").Value = "0:0:10"
End Sub
Sub stoptimer()
On Error Resume Next
    Application.OnTime Now + TimeValue("00:00:01"), "nexttick", , False
End Sub
Attached Files
File Type: xlsm EXCEL BINGO PROGRAM Sep 5 20181.xlsm (30.4 KB, 12 views)
Reply With Quote
  #12  
Old 09-16-2018, 01:33 PM
ElCoyote ElCoyote is offline 10 Second Timer for Calling Bingo Windows 7 64bit 10 Second Timer for Calling Bingo Office 2010 64bit
Novice
10 Second Timer for Calling Bingo
 
Join Date: Mar 2017
Posts: 6
ElCoyote is on a distinguished road
Default

Logit & NoSparks..... LOGIT has hit in on the head for what I was looking for.......thank you, both!
Reply With Quote
  #13  
Old 09-16-2018, 01:37 PM
Logit Logit is offline 10 Second Timer for Calling Bingo Windows 10 10 Second Timer for Calling Bingo Office 2007
Expert
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

.
You are welcome. Glad we could help.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
#REF Error when calling VBA Function RodneyJ Excel Programming 1 01-21-2016 12:42 PM
Re-Calling Sent Email freschij Outlook 1 12-06-2010 09:16 PM
calling images with a button sammer021486 PowerPoint 0 01-13-2010 08:24 AM
10 Second Timer for Calling Bingo first timer papamadre Forum Support 1 10-24-2009 06:26 AM
Bingo Cards office_mike Excel 1 11-29-2008 10:17 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 10:37 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft