userform - active sheet

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... )

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]...
>
> 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... )
>
>
> --
> gillesdhooghe
> ------------------------------------------------------------------------
> gillesdhooghe's Profile:
> http://www.excelforum.com/member.php...o&userid=30667
> View this thread: http://www.excelforum.com/showthread...hreadid=503283
>

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]...
>
> 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... )
>
>
> --
> gillesdhooghe
> ------------------------------------------------------------------------
> gillesdhooghe's Profile:
http://www.excelforum.com/member.php...o&userid=30667
> View this thread: http://www.excelforum.com/showthread...hreadid=503283
>

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]...
>
> 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... )
>
>
> --
> gillesdhooghe
> ------------------------------------------------------------------------
> gillesdhooghe's Profile:
http://www.excelforum.com/member.php...o&userid=30667
> View this thread: http://www.excelforum.com/showthread...hreadid=503283
>