Am I missing something in my range definition???

[TDC21]

Hey everyone, sorry, my VBA is pretty limited. I am trying to create a new update form by reverse engineering a working form. I am getting error '1004' "Select method of Range class failed". The error is from "rng.Select" but I dont see anything wrong with the code where the range is defined, IDK.

As a side note, I also have time stamp to record the entry date but if possible I would like to switch that out for a date picker but I can seem to find a good way to do that. Anything obvious I might be missing or is this not an option anymore.


Code

Private Sub CommandButton1_Click()


Dim oNewRow As ListRow
Dim rng As Range
Set rng = ThisWorkbook.Worksheets("DATA_SHEET").Range("TempTbl")
rng.Select
Set oNewRow = Selection.ListObject.ListRows.Add(AlwaysInsert:=True)
With ws
oNewRow.Range.Cells(1, 1).Value = Me.TempID
oNewRow.Range.Cells(1, 6).Value = Me.StatusC
oNewRow.Range.Cells(1, 2).Value = Now
End With


Me.TempID = ""
Me.StatusC = ""


End Sub

 

[Rick Rothstein]

You cannot select a range on a sheet that is not the active sheet. Is DATA_SHEET the active sheet when your code runs?

 

[jproffer]

Pretty sure you'll have to define the worksheet and range separately.

Set ws = ThisWorkbook.Worksheets("DATA_SHEET")
Set rng = Range("TempTbl")

and really, if you're only using it once, like you're showing...you don't need all that. Just use:

ThisWorkbook.Worksheets("DATA_SHEET").Range("TempTbl").Select

And really, you don't need to select it anyway...just do what you want to do to it.

ThisWorkbook.Worksheets("DATA_SHEET").Range("TempTbl").Copy ' (or whatever)

EDIT: Guess I had it wrong about setting them separately. I didn't even think about it being the active sheet or not

 

[TDC21]

Thanks Rick, that is exactly what the issue was. The code was being called from a button on a separate sheet. I wish I was better and did not have to ask but is there a way to modify the code to allow this? It would be nice to keep users away from the source data being generated.

 

[Rick Rothstein]

I wish I was better and did not have to ask but is there a way to modify the code to allow this? It would be nice to keep users away from the source data being generated.

The post by jproffer had it wrong about setting the references, but he was right on target with his last comment... "And really, you don't need to select it anyway...just do what you want to do to it {the range}". A range is a defined object in VBA, so you do not have to select it in order to work with it... there are a couple of exceptions, but basically whatever you can do to the Selection object you can do directly to the object that you selected originally. In other words, whenever you see yourself doing this...

Sheets("Tabname").Range("SomeAddress").Select
Selection.Something....

you should do this instead...

Sheets("Tabname").Range("SomeAddress").Something...

You can omit the sheet reference if every the range is always going to be on the active sheet.