Help with code please

Greg · 06-19-2006, 11:30 PM

Hi I am wondering how to get this macro to retrigger when the user clicks
yes in the Dim Queri section. I have a message box appear and the user can
choose yes or no, but how do I get it to restart from the beggining again?

Private Sub Image20_Click()

Sheets("SEASON").Select
Rows("2:2").Select
Selection.Insert Shift:=xlDown
Range("D2").Select
Dim ans
Dim PLAYERS As String
Sheet5.Activate
PLAYERS = InputBox("WHAT IS THE PLAYERS REGISTRATION NUMBER?")
On Error Resume Next

ans = Application.Match(CLng(PLAYERS), Range("A:A"), 0)
If Not IsError(ans) Then
Sheet3.Range("A2").Value = Application.Index(Range("A:A"), ans)
Sheet3.Range("B2").Value = Application.Index(Range("B:B"), ans)
Sheet3.Range("C2").Value = Application.Index(Range("C:C"), ans)

Else
End If
On Error GoTo 0

Sheets("SEASON").Select
ActiveCell.FormulaR1C1 = "0"
Range("E2").Select
ActiveCell.FormulaR1C1 = "0"
Range("F2").Select
ActiveCell.FormulaR1C1 = "0"
Range("G2").Select
ActiveCell.FormulaR1C1 = "0"
Range("H2").Select
ActiveCell.FormulaR1C1 = "0"
Range("F2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-2]>0,SUM(RC[-1]/RC[-2]),0)"
Range("F2").Select
Selection.Copy
Range("I2").Select
ActiveSheet.Paste
Range("L2").Select
ActiveSheet.Paste
Range("O2").Select
ActiveSheet.Paste
Range("J2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "0"
Range("K2").Select
ActiveCell.FormulaR1C1 = "0"
Range("M2").Select
ActiveCell.FormulaR1C1 = "=RC[-9]+RC[-6]+RC[-3]"
Range("M2").Select
Selection.Copy
Range("N2").Select
ActiveSheet.Paste
Range("P2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "0"
Range("Q2").Select
ActiveCell.FormulaR1C1 = "0"
Range("R2").Select
ActiveCell.FormulaR1C1 = "0"
Range("S2").Select
ActiveCell.FormulaR1C1 = "0"
Range("T2").Select
ActiveCell.FormulaR1C1 = "=IF(SUM(RC[-16]/4)>6,""Q"",""NQ"")"
Range("U2").Select
ActiveCell.FormulaR1C1 = "=IF(SUM(RC[-14]/8)>6,""Q"",""NQ"")"
Range("V2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-19]=""M"",RC[-6],0)"
Range("W2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-20]=""F"",RC[-7],0)"
Cells.Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("A1").Select

' HERE IS THE PART I AM UNSURE OF?

Dim QUERI
QUERI = MsgBox("IS THERE ANY OTHER PLAYER'S TO ADD?", vbYesNo)
If QUERI = vbYes Then
End If

If QUERI = vbNo Then
Unload Me
MAIN.Show
End If

Thanks

Greg

JMB · 06-20-2006, 12:30 AM

You could use a Do/Loop

Do
..procedures you want repeated
Loop Until Queri = vbno

"Greg" wrote:

> Hi I am wondering how to get this macro to retrigger when the user clicks
> yes in the Dim Queri section. I have a message box appear and the user can
> choose yes or no, but how do I get it to restart from the beggining again?
>
> Private Sub Image20_Click()
>
> Sheets("SEASON").Select
> Rows("2:2").Select
> Selection.Insert Shift:=xlDown
> Range("D2").Select
> Dim ans
> Dim PLAYERS As String
> Sheet5.Activate
> PLAYERS = InputBox("WHAT IS THE PLAYERS REGISTRATION NUMBER?")
> On Error Resume Next
>
>
> ans = Application.Match(CLng(PLAYERS), Range("A:A"), 0)
> If Not IsError(ans) Then
> Sheet3.Range("A2").Value = Application.Index(Range("A:A"), ans)
> Sheet3.Range("B2").Value = Application.Index(Range("B:B"), ans)
> Sheet3.Range("C2").Value = Application.Index(Range("C:C"), ans)
>
> Else
> End If
> On Error GoTo 0
>
>
> Sheets("SEASON").Select
> ActiveCell.FormulaR1C1 = "0"
> Range("E2").Select
> ActiveCell.FormulaR1C1 = "0"
> Range("F2").Select
> ActiveCell.FormulaR1C1 = "0"
> Range("G2").Select
> ActiveCell.FormulaR1C1 = "0"
> Range("H2").Select
> ActiveCell.FormulaR1C1 = "0"
> Range("F2").Select
> ActiveCell.FormulaR1C1 = "=IF(RC[-2]>0,SUM(RC[-1]/RC[-2]),0)"
> Range("F2").Select
> Selection.Copy
> Range("I2").Select
> ActiveSheet.Paste
> Range("L2").Select
> ActiveSheet.Paste
> Range("O2").Select
> ActiveSheet.Paste
> Range("J2").Select
> Application.CutCopyMode = False
> ActiveCell.FormulaR1C1 = "0"
> Range("K2").Select
> ActiveCell.FormulaR1C1 = "0"
> Range("M2").Select
> ActiveCell.FormulaR1C1 = "=RC[-9]+RC[-6]+RC[-3]"
> Range("M2").Select
> Selection.Copy
> Range("N2").Select
> ActiveSheet.Paste
> Range("P2").Select
> Application.CutCopyMode = False
> ActiveCell.FormulaR1C1 = "0"
> Range("Q2").Select
> ActiveCell.FormulaR1C1 = "0"
> Range("R2").Select
> ActiveCell.FormulaR1C1 = "0"
> Range("S2").Select
> ActiveCell.FormulaR1C1 = "0"
> Range("T2").Select
> ActiveCell.FormulaR1C1 = "=IF(SUM(RC[-16]/4)>6,""Q"",""NQ"")"
> Range("U2").Select
> ActiveCell.FormulaR1C1 = "=IF(SUM(RC[-14]/8)>6,""Q"",""NQ"")"
> Range("V2").Select
> ActiveCell.FormulaR1C1 = "=IF(RC[-19]=""M"",RC[-6],0)"
> Range("W2").Select
> ActiveCell.FormulaR1C1 = "=IF(RC[-20]=""F"",RC[-7],0)"
> Cells.Select
> Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess,
> _
> OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
> DataOption1:=xlSortNormal
> Range("A1").Select
>
> ' HERE IS THE PART I AM UNSURE OF?
>
> Dim QUERI
> QUERI = MsgBox("IS THERE ANY OTHER PLAYER'S TO ADD?", vbYesNo)
> If QUERI = vbYes Then
> End If
>
>
> If QUERI = vbNo Then
> Unload Me
> MAIN.Show
> End If
>
> Thanks
>
> Greg
>
>
>

Greg · 06-20-2006, 01:10 AM

Thanks for that

Greg
"JMB" <[email protected]> wrote in message
news:[email protected]...
> You could use a Do/Loop
>
> Do
> ..procedures you want repeated
> Loop Until Queri = vbno
>
>
> "Greg" wrote:
>
>> Hi I am wondering how to get this macro to retrigger when the user clicks
>> yes in the Dim Queri section. I have a message box appear and the user
>> can
>> choose yes or no, but how do I get it to restart from the beggining
>> again?
>>
>> Private Sub Image20_Click()
>>
>> Sheets("SEASON").Select
>> Rows("2:2").Select
>> Selection.Insert Shift:=xlDown
>> Range("D2").Select
>> Dim ans
>> Dim PLAYERS As String
>> Sheet5.Activate
>> PLAYERS = InputBox("WHAT IS THE PLAYERS REGISTRATION NUMBER?")
>> On Error Resume Next
>>
>>
>> ans = Application.Match(CLng(PLAYERS), Range("A:A"), 0)
>> If Not IsError(ans) Then
>> Sheet3.Range("A2").Value = Application.Index(Range("A:A"), ans)
>> Sheet3.Range("B2").Value = Application.Index(Range("B:B"), ans)
>> Sheet3.Range("C2").Value = Application.Index(Range("C:C"), ans)
>>
>> Else
>> End If
>> On Error GoTo 0
>>
>>
>> Sheets("SEASON").Select
>> ActiveCell.FormulaR1C1 = "0"
>> Range("E2").Select
>> ActiveCell.FormulaR1C1 = "0"
>> Range("F2").Select
>> ActiveCell.FormulaR1C1 = "0"
>> Range("G2").Select
>> ActiveCell.FormulaR1C1 = "0"
>> Range("H2").Select
>> ActiveCell.FormulaR1C1 = "0"
>> Range("F2").Select
>> ActiveCell.FormulaR1C1 = "=IF(RC[-2]>0,SUM(RC[-1]/RC[-2]),0)"
>> Range("F2").Select
>> Selection.Copy
>> Range("I2").Select
>> ActiveSheet.Paste
>> Range("L2").Select
>> ActiveSheet.Paste
>> Range("O2").Select
>> ActiveSheet.Paste
>> Range("J2").Select
>> Application.CutCopyMode = False
>> ActiveCell.FormulaR1C1 = "0"
>> Range("K2").Select
>> ActiveCell.FormulaR1C1 = "0"
>> Range("M2").Select
>> ActiveCell.FormulaR1C1 = "=RC[-9]+RC[-6]+RC[-3]"
>> Range("M2").Select
>> Selection.Copy
>> Range("N2").Select
>> ActiveSheet.Paste
>> Range("P2").Select
>> Application.CutCopyMode = False
>> ActiveCell.FormulaR1C1 = "0"
>> Range("Q2").Select
>> ActiveCell.FormulaR1C1 = "0"
>> Range("R2").Select
>> ActiveCell.FormulaR1C1 = "0"
>> Range("S2").Select
>> ActiveCell.FormulaR1C1 = "0"
>> Range("T2").Select
>> ActiveCell.FormulaR1C1 = "=IF(SUM(RC[-16]/4)>6,""Q"",""NQ"")"
>> Range("U2").Select
>> ActiveCell.FormulaR1C1 = "=IF(SUM(RC[-14]/8)>6,""Q"",""NQ"")"
>> Range("V2").Select
>> ActiveCell.FormulaR1C1 = "=IF(RC[-19]=""M"",RC[-6],0)"
>> Range("W2").Select
>> ActiveCell.FormulaR1C1 = "=IF(RC[-20]=""F"",RC[-7],0)"
>> Cells.Select
>> Selection.Sort Key1:=Range("A2"), Order1:=xlAscending,
>> Header:=xlGuess,
>> _
>> OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
>> DataOption1:=xlSortNormal
>> Range("A1").Select
>>
>> ' HERE IS THE PART I AM UNSURE OF?
>>
>> Dim QUERI
>> QUERI = MsgBox("IS THERE ANY OTHER PLAYER'S TO ADD?", vbYesNo)
>> If QUERI = vbYes Then
>> End If
>>
>>
>> If QUERI = vbNo Then
>> Unload Me
>> MAIN.Show
>> End If
>>
>> Thanks
>>
>> Greg
>>
>>
>>

**kartune85** · 06-20-2006, 01:10 AM

Maybe you could try putting your code in a function and calling that function when the image is clicked and then you should also be able to call the function later on when the user clicks "yes".

E.g.:

Sub AddPlayer()
Sheets("SEASON").Select
Rows("2:2").Select
Selection.Insert Shift:=xlDown
Range("D2").Select...

etc...
end Sub

Then call it from within:
Private Sub Image20_Click()
Call PlayerAdd
End Sub

Just a thought, I don't know how you've set it up.

Kartune85

Greg · 06-20-2006, 02:30 AM

I sort of did that but I used the activation function of a userform and had
it turn it self off and on to activate the proceddure again.

Thanks for the advice

Greg
"kartune85" <[email protected]> wrote
in message news:[email protected]...
>
> Maybe you could try putting your code in a function and calling that
> function when the image is clicked and then you should also be able to
> call the function later on when the user clicks "yes".
>
> E.g.:
>
> Sub AddPlayer()
> Sheets("SEASON").Select
> Rows("2:2").Select
> Selection.Insert Shift:=xlDown
> Range("D2").Select...
>
> etc...
> end Sub
>
>
> Then call it from within:
> Private Sub Image20_Click()
> Call PlayerAdd
> End Sub
>
> Just a thought, I don't know how you've set it up.
>
> Kartune85
>
>
> --
> kartune85
> ------------------------------------------------------------------------
> kartune85's Profile:
> http://www.excelforum.com/member.php...o&userid=35586
> View this thread: http://www.excelforum.com/showthread...hreadid=553483
>