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).O ffset(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

userform - active sheet
 

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"
wrote in message
news:gillesdhooghe.21xkrp_1137756337.725@excelforu m-nospam.com...

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).O ffset(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

userform - active sheet
 

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"
wrote in message
news:gillesdhooghe.21xkrp_1137756337.725@excelforu m-nospam.com...

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).O ffset(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

userform - active sheet
 

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

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

or just set a cell object to that cell

Set myCell = Worksheets(\"Sheet2\").Range(\"B3\").End(xlDown).O ffset(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"
wrote in message
news:gillesdhooghe.21xkrp_1137756337.725@excelforu m-nospam.com...

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).O ffset(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