userform - active sheet

[gillesdhooghe]

Hi All,


first post here, playing around in VBA, and I have the following
question. Well, I'll first explain the situation:

Sheet2: Database with range of cells
Userform1: contains Textbox1 in which user can enter data to be added
to database on Sheet2
Textbox1: when data is added, this is first checked against the
database for identical entries, if not, then data is added to database
and userform is closed.

OK, so, here is my question:
I use the following code to select the cell in which the data is to be
added on Sheet2:

Worksheets(\"Sheet2\").Range(\"B3\").End(xlDown).Offset(1,
0).Select

Problem is that this only works when Sheet2 is activated. When I open
the userform while Sheet1 is active, this gives me the error:
\"Run-time error '1004': Select method of Range class failed\" I want
Sheet2 to stay hidden, because it's kinda not-very-pretty. (Btw, what
is the code to hide the worksheet when the file is opened?)


So, *in sum, how can I use the userform without necessarily having to
activate the worksheet containing the database?*

And an awesome-mega-giant thanks for all the threads I have already
been able to use !!!



Thanks,

Gilles


(P.S.: sorry for the sloppy title of the thread... )

 

[Damon Longworth]

You can activate the worksheet, then find the last cell.

--
Damon Longworth

2006 East Coast Excel User Conference
April 19/21st, 2006
Holiday Inn, Boardwalk
Atlantic City, New Jersey
Early Bird Registration Now Open!!
www.ExcelUserConference.com

2006 UK Excel User Conference
Summer, 2006
London, England
"gillesdhooghe" <[email protected]>
wrote in message
news:[email protected]...

 

[Tom Ogilvy]

There is no reason to select the cell to assign a value to it:

Dim rng as Range

set rng = Worksheets(\"Sheet2\").Range(\"B3\") _
.End(xlDown).Offset(1,0)
rng.Value = Userform1.TextBox1
rng.offset(0,1).Value = Userform1.TextBox2.Value

--
Regards,
Tom Ogilvy


"gillesdhooghe" <[email protected]>
wrote in message
news:[email protected]...

 

[Bob Phillips]

Avoid the select. You can work with non-active worksheets, just don't select
them. For instance

myVar = Worksheets(\"Sheet2\").Range(\"B3\").End(xlDown).Offset(1,0).Value

or just set a cell object to that cell

Set myCell = Worksheets(\"Sheet2\").Range(\"B3\").End(xlDown).Offset(1,0)

then you can use myCell to read or write to.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"gillesdhooghe" <[email protected]>
wrote in message
news:[email protected]...