PDA

View Full Version : Solved: Only use CommandButton Once



sooty8
03-23-2009, 12:59 PM
Hi All

Is it possible to alter the code below to restrict just one entry on the day I would be using the code. For example I could receive 6 items of data to enter however only the first entry on the day is the one that matters to this particular code. I can enter the other 5 items using a different CommandButton -- but when entering data I occasionally use the one one listed below by mistake and it then I have to start all over again. Perhaps a message box telling me I have already used it today??




Private Sub Av1_Click()
cbo71.Value = txtTitle.Text
Sheets("OB;In;Av").Select
Dim c As Range, Cntrl As Control
Set c = Columns(1).Find(cbo71, LookIn:=xlValues, lookat:=xlWhole)
c.Offset(, 2) = c.Offset(, 2) + (txtMiles * 105600) + (txtYards * 60)
c.Offset(, 3) = c.Offset(, 3) + (TextCalcHrs * 3600) + (TextCalcMins * 60) + (TextCalcSecs)
c.Offset(, 4) = c.Offset(, 2) / c.Offset(, 3)
'Count races
c.Offset(, 5) = c.Offset(, 5) + 1

End Sub


Thanks

Sooty8

Bob Phillips
03-23-2009, 01:37 PM
Private Sub Av1_Click()
Static fUsed As Boolean

If Not fUsed Then

cbo71.Value = txtTitle.Text
Sheets("OB;In;Av").Select
Dim c As Range, Cntrl As Control
Set c = Columns(1).Find(cbo71, LookIn:=xlValues, lookat:=xlWhole)
c.Offset(, 2) = c.Offset(, 2) + (txtMiles * 105600) + (txtYards * 60)
c.Offset(, 3) = c.Offset(, 3) + (TextCalcHrs * 3600) + (TextCalcMins * 60) + (TextCalcSecs)
c.Offset(, 4) = c.Offset(, 2) / c.Offset(, 3)
'Count races
c.Offset(, 5) = c.Offset(, 5) + 1
fUsed = True
End If
End Sub

[/vba]

sooty8
03-23-2009, 01:59 PM
Hi Xld

Many thanks worked spot as usual - But I have dropped a clanger I have a listbox LisBox1 with names of the sender of the info e.g. Rep1, Rep2, Rep3 etc in the LisBox1 Change Event is it possible to release the button Av1 so as I can use it for the next selection.

Many Thanks

Sooty8

Bob Phillips
03-23-2009, 02:05 PM
It would be easiest to use a Public module level variable rather than the Static procedure varibale in that case, and unset it when the Listbox is changed.

sooty8
03-24-2009, 05:14 AM
Hi Xld

Been trying for about an hour to get your recent advice to work -- no such luck can you possibly guide me in the right direction

Many Thanks

Sooty8

Bob Phillips
03-24-2009, 05:20 AM
Just off the top, change that routine to



Private Sub Av1_Click()

If Not fUsed Then

cbo71.Value = txtTitle.Text
Sheets("OB;In;Av").Select
Dim c As Range, Cntrl As Control
Set c = Columns(1).Find(cbo71, LookIn:=xlValues, lookat:=xlWhole)
c.Offset(, 2) = c.Offset(, 2) + (txtMiles * 105600) + (txtYards * 60)
c.Offset(, 3) = c.Offset(, 3) + (TextCalcHrs * 3600) + (TextCalcMins * 60) + (TextCalcSecs)
c.Offset(, 4) = c.Offset(, 2) / c.Offset(, 3)
'Count races
c.Offset(, 5) = c.Offset(, 5) + 1
fUsed = True
End If
End Sub


Declare the variable as a public variable in a standard code module (or a module level variable if it ia all in a single form )



Public fUsed As Boolean


and finally,I the Listbox change event set the flag



Private Sub ListBox1_Change()

fUsed = False
'do the stuff, the button routine will reset it
End Sub

sooty8
03-24-2009, 09:15 AM
Hi Xld

Now I've caught up with all the entries for today -- Your code and expertise has once again solved my problem --

Many Thanks
Much Appreciated

Sooty8