400 Error

[Brett]

Bit of basic code but i cannot clear the contents of the range because they
won't select with the current code i have.

Sub Button19_Click()

Dim name As String
Dim pswd As String

name = Sheet1.TextBox1.Value
pswd = Sheet1.TextBox2.Value

If pswd = "Alphabet" Then
Sheets("Record").Visible = True

Sheets("Times tables").Select
Range("G3:G12").Select
Selection.ClearContents
Range("E1").Select

Sheet1.TextBox1.Value = ""
Sheet1.TextBox2.Value = ""


Else
ActiveWorkbook.Close
Application.Quit

End If

End Sub

I wanted to focus on using the code

Sheets("Times tables").Select
Range("G3:G12").Select
Selection.ClearContents
Range("E1").Select

For my project, however, i get a 400 error and my code does not work. If i
remove the following:

Range("G3:G12").Select
Selection.ClearContents
Range("E1").Select

The code seems fine and the Times tables page is selected, but i cannot
select cells.

Any help is much appreciated.

 

[Mike H]

Hi,

Don.t select, try it like this

Sheets("Times tables").Range("G3:G12").ClearContents

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Jacob Skaria]

You dont need to select the sheet or select the range to clear contents of a
specific range. Instead try the below piece of code......The sheet need not
be activated...

Sheets("Times tables").Range("G3:G12").ClearContents

 

[OssieMac]

Hi Brett,

Answers by Mike and Jacob are correct in that it is almost never necessary
to select a range to process the data in those ranges. However, perhaps an
explanation as to why you cannot select the range. My guess is that the code
is in the Sheet1 Module and because of this, any reference to a range
defaults to Sheet1 unless specified otherwise. Therefore use With / End With
to tie the ranges to the required Parent sheet.

Because you are selecting Range("E1") on Sheets("Times tables") after the
ClearContents code I thought that maybe you have a valid reason to select in
this case so that it is then ready for the user to continue with whatever
needs to be done next and hense my real reason for this answer.

Sub Button19_Click()
Dim name As String
Dim pswd As String

name = Sheet1.TextBox1.Value
pswd = Sheet1.TextBox2.Value

If pswd = "Alphabet" Then
Sheets("Record").Visible = True

With Sheets("Times tables")
.Select
.Range("G3:G12").Select
Selection.ClearContents
.Range("E1").Select
End With

Sheet1.TextBox1.Value = ""
Sheet1.TextBox2.Value = ""

Else
ActiveWorkbook.Close
Application.Quit

End If

End Sub