code error

ninjak

Board Regular
Joined
Mar 13, 2003
Messages
82
this is the code that i have

Sub New1HShooter()
Sheets("One Handed").Select
tempCol = 2
If Cells(3, tempCol) = "" Then
Sheets("template").Select
Range("B2:B58").Select
Selection.Copy
Sheets("One Handed").Select
Range("B2:B58").Select
ActiveSheet.Paste
Else
While Cells(3, tempCol) <> ""
tempCol = tempCol + 1
Wend
Sheets("template").Select
Range("B2:B58").Select
Selection.Copy
Sheets("One Handed").Select
Cells(2, tempCol).Select
ActiveSheet.Paste
End If
tempName = InputBox("Enter Shooter's Name", "New Shooter Entry")
Cells(3, tempCol) = tempName
Sheets("Extra Scores").Select
Columns("F:I").Select
Selection.Insert Shift:=xlToRight
Columns("B:E").Select
Selection.Copy
Columns("F:I").Select
ActiveSheet.Paste
Cells(4, 7) = tempName
Cells(5, 7) = "1 Handed"
Cells(1, 1).Select
Sheets("Main Menu").Select
Cells(1, 1).Select
End Sub

what happens is that if i do not enter a name in and i hit cancel it gives me a error can somebody help me fix this so it would clear the data from the sheet and take me back to the main page please
i think mabe change the option of the input box where it ask for the name maybe not sure hope somebody can help out
Thanks Al
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

ninjak

Board Regular
Joined
Mar 13, 2003
Messages
82
Well that stoped the error but it still left me on the page and left the data there just has no name. is there a way to make a option before it goes and adds the data from the temp to the sheet or is there a way to remove the data if i hit cancel and it take me back to the main page ?

AL
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
Maybe follow on what JPG posted and try something like:

If Len(tempName) = 0 Then Application.Goto Reference:=['Main Page'!A1]

Also note that

Range("B2:B58").Select
Selection.Copy

Can be shortened to Range("B2:B58").Copy

Generally, anytime Select and Selection immediately follow each other, they can be eliminated.

Hope that helps,

Smitty

(Sorry for the brief look--Post back if that doesn't help...)
 

ninjak

Board Regular
Joined
Mar 13, 2003
Messages
82

ADVERTISEMENT

well seem to work but it still does not delete the data it copys what i need it too do is if you hit cancel it removes the data it copied and takes you back to the main page ..

Thanks AL
 

Chitosunday

Well-known Member
Joined
Jul 14, 2003
Messages
1,017
Sub New1HShooter()
Sheets("One Handed").Select
tempcol = 2
If Cells(3, tempcol) = "" Then
Sheets("template").Range("B2:B58").Copy Range("B2:B58")
Else
While Cells(3, tempcol) <> ""
tempcol = tempcol + 1
Wend
Sheets("template").Range("B2:B58").Copy Cells(2, tempcol)
End If
tempname = InputBox("Enter Shooter's Name", "New Shooter Entry")
If tempname = "" Then
Range(Cells(2, tempcol), Cells(58, tempcol)).Clear
Else
Cells(3, tempcol) = tempname
Sheets("Extra Scores").Select
Columns("F:I").Select
Selection.Insert Shift:=xlToRight
Columns("B:E").Copy Columns("F:I")
Cells(4, 7) = tempname
Cells(5, 7) = "1 Handed"
Cells(1, 1).Select
End If
Sheets("Main Menu").Select
Cells(1, 1).Select
End Sub
 

ninjak

Board Regular
Joined
Mar 13, 2003
Messages
82
Thanks for all the input so far :), but that new code works somewhat it takes you back to the main menu but does not clear the data from the copy script ...I have not been able to figure this out,

AL
 

Chitosunday

Well-known Member
Joined
Jul 14, 2003
Messages
1,017
Can you elaborate more on what you want to do. Per my testing , my codes clear the data it copied when you enter cancel. Example of elaboration.
My code will do the following:
It looks for empty cell starting from b3 of sheets "one handed" to next column of row 3 until it finds an empty cell.
It then copies the data in sheets "template" cell b2:b58 to
sheets "one handed" cell where it find the empty cell.
It then ask for a name of shooter's name and if you press cancel, it deletes the data copied to sheet "one handed" but not the data in sheets "template". However, if you give a shooter's name, it then overwrite the row 3 column of the empty cell with the shooter's name then go to sheet "extra scorer where it insert four columns from column f then put the shooter's name in cell g4 and text "1 handed in g5. it then goes to cell a1 of extra scores then to cell a1 of main menu
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,561
Messages
5,765,109
Members
425,260
Latest member
worldbfreebase

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top