Hi, how do I "push" a value into a cell?

What do I mean?

I know that if I have cell A1, I can make the contents of
that cell equal to the value of any other cell in a
workbook. The easiest way is to enter

the formula in cell A1, "=B1" (where B1 holds the
contents that I want A1 to hold). I can even make a
conditional formula, eg if A1 is empty, fill it

with the contents of B1, else leave it alone.

But, what if I have a range of cells, eg A1 to A9 that I
want to fill, but only from top down, and only if the
cell is empty and the previous cell in the

range is not? eg;

A1 = Fred
A2 = John
A3 = Mary
A4 = James
A5 = ""
A6 = ""
A7 = ""
A8 = ""
A9 = ""

I want A5, the next empty cell in the range, to hold the
name Anne, but not the remainder of the empty cells?

I can set up a flag, say in the range C1 to C9, so that
if any cells in the A1 to A9 range are empty the flag
cell shows "0", or if any are not, the

flag cell shows "1", or if the cell above is empty, the
flag cell shows "2", eg

0 = empty, 1 = not empty, 2 = empty but the cell above
is also empty

C1 = 1 (A1 is not empty)
C2 = 1 (A2 is not empty)
C3 = 1 (A3 is not empty)
C4 = 1 (A4 is not empty)
C5 = 0 (A5 is empty)
C6 = 2 (A6 is empty but so is the previous cell)
C7 = 2 (and so on)
C8 = 2
C9 = 2


This way, I can use the MATCH function to find out that
the next empty cell is in row 5.

But then, if a user inputs into cell D1 the name Anne,
how do I "push" that value from D1 into the next
available empty cell, A5, and not all the

other empty cells, A6 to A9?

I feel I should be using the INDIRECT function somehow,
but I cannot get my head around the problem.

Any guidance would be appreciated.

Er.... I would rather not use VBA if I can avoid it. My
brain is too small to start to learn yet another
programming style, :)

Many thanks,

Mark

Mark,

Label your column of names with a title (say Names)
The names you filled are now in A2:A5

Select the range of names (and maybe a few addional rows.
Data / Form ; Click OKbutton.
On the inputform now click New record and type Anne and push the Enter key

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

"Mark Flaxman" wrote in message
...
Hi, how do I "push" a value into a cell?

What do I mean?

I know that if I have cell A1, I can make the contents of
that cell equal to the value of any other cell in a
workbook. The easiest way is to enter

the formula in cell A1, "=B1" (where B1 holds the
contents that I want A1 to hold). I can even make a
conditional formula, eg if A1 is empty, fill it

with the contents of B1, else leave it alone.

But, what if I have a range of cells, eg A1 to A9 that I
want to fill, but only from top down, and only if the
cell is empty and the previous cell in the

range is not? eg;

A1 = Fred
A2 = John
A3 = Mary
A4 = James
A5 = ""
A6 = ""
A7 = ""
A8 = ""
A9 = ""

I want A5, the next empty cell in the range, to hold the
name Anne, but not the remainder of the empty cells?

I can set up a flag, say in the range C1 to C9, so that
if any cells in the A1 to A9 range are empty the flag
cell shows "0", or if any are not, the

flag cell shows "1", or if the cell above is empty, the
flag cell shows "2", eg

0 = empty, 1 = not empty, 2 = empty but the cell above
is also empty

C1 = 1 (A1 is not empty)
C2 = 1 (A2 is not empty)
C3 = 1 (A3 is not empty)
C4 = 1 (A4 is not empty)
C5 = 0 (A5 is empty)
C6 = 2 (A6 is empty but so is the previous cell)
C7 = 2 (and so on)
C8 = 2
C9 = 2


This way, I can use the MATCH function to find out that
the next empty cell is in row 5.

But then, if a user inputs into cell D1 the name Anne,
how do I "push" that value from D1 into the next
available empty cell, A5, and not all the

other empty cells, A6 to A9?

I feel I should be using the INDIRECT function somehow,
but I cannot get my head around the problem.

Any guidance would be appreciated.

Er.... I would rather not use VBA if I can avoid it. My
brain is too small to start to learn yet another
programming style, :)

Many thanks,

Mark

Hi m8,

Thanks for your advice.

I had tried Data / Form / New but I don't think this is
the answer I need.

I have now "named" the range A2 to A10 with a name, and
tried Data / Form again, but it's not working for me. It
is probably me, but I am not sure I understand your
solution to my problem.

I need to capture what has been input by someone else
elsewhere on the form. Data / Form doesn't seem to be
able to capture from a different cell reference.

Bu thanks anyway for your time and advice

Mark Flaxman

-----Original Message-----
Mark,

Label your column of names with a title (say Names)
The names you filled are now in A2:A5

Select the range of names (and maybe a few addional rows.
Data / Form ; Click OKbutton.
On the inputform now click New record and type Anne and
push the Enter key

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

"Mark Flaxman" wrote in
message
...
Hi, how do I "push" a value into a cell?

What do I mean?

I know that if I have cell A1, I can make the contents
of
that cell equal to the value of any other cell in a
workbook. The easiest way is to enter

the formula in cell A1, "=B1" (where B1 holds the
contents that I want A1 to hold). I can even make a
conditional formula, eg if A1 is empty, fill it

with the contents of B1, else leave it alone.

But, what if I have a range of cells, eg A1 to A9 that
I
want to fill, but only from top down, and only if the
cell is empty and the previous cell in the

range is not? eg;

A1 = Fred
A2 = John
A3 = Mary
A4 = James
A5 = ""
A6 = ""
A7 = ""
A8 = ""
A9 = ""

I want A5, the next empty cell in the range, to hold
the
name Anne, but not the remainder of the empty cells?

I can set up a flag, say in the range C1 to C9, so that
if any cells in the A1 to A9 range are empty the flag
cell shows "0", or if any are not, the

flag cell shows "1", or if the cell above is empty, the
flag cell shows "2", eg

0 = empty, 1 = not empty, 2 = empty but the cell
above
is also empty

C1 = 1 (A1 is not empty)
C2 = 1 (A2 is not empty)
C3 = 1 (A3 is not empty)
C4 = 1 (A4 is not empty)
C5 = 0 (A5 is empty)
C6 = 2 (A6 is empty but so is the previous cell)
C7 = 2 (and so on)
C8 = 2
C9 = 2


This way, I can use the MATCH function to find out that
the next empty cell is in row 5.

But then, if a user inputs into cell D1 the name Anne,
how do I "push" that value from D1 into the next
available empty cell, A5, and not all the

other empty cells, A6 to A9?

I feel I should be using the INDIRECT function somehow,
but I cannot get my head around the problem.

Any guidance would be appreciated.

Er.... I would rather not use VBA if I can avoid it. My
brain is too small to start to learn yet another
programming style, :)

Many thanks,

Mark


.

Mark,

I don't think that you can do what you want to do ( append to a range the
value of a *cell* elsewhere on the sheet) other than by VBA.

The solution I gave (and that the reason why i gave it) does this, although
the input then is not from a cell, but via a form. I'l keep an eye on it to
watch if someone else does come with a solution.

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

"Mark Flaxman" wrote in message
...

Hi m8,

Thanks for your advice.

I had tried Data / Form / New but I don't think this is
the answer I need.

I have now "named" the range A2 to A10 with a name, and
tried Data / Form again, but it's not working for me. It
is probably me, but I am not sure I understand your
solution to my problem.

I need to capture what has been input by someone else
elsewhere on the form. Data / Form doesn't seem to be
able to capture from a different cell reference.

Bu thanks anyway for your time and advice

Mark Flaxman

-----Original Message-----
Mark,

Label your column of names with a title (say Names)
The names you filled are now in A2:A5

Select the range of names (and maybe a few addional rows.
Data / Form ; Click OKbutton.
On the inputform now click New record and type Anne and
push the Enter key

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

"Mark Flaxman" wrote in
message
...
Hi, how do I "push" a value into a cell?

What do I mean?

I know that if I have cell A1, I can make the contents
of
that cell equal to the value of any other cell in a
workbook. The easiest way is to enter

the formula in cell A1, "=B1" (where B1 holds the
contents that I want A1 to hold). I can even make a
conditional formula, eg if A1 is empty, fill it

with the contents of B1, else leave it alone.

But, what if I have a range of cells, eg A1 to A9 that
I
want to fill, but only from top down, and only if the
cell is empty and the previous cell in the

range is not? eg;

A1 = Fred
A2 = John
A3 = Mary
A4 = James
A5 = ""
A6 = ""
A7 = ""
A8 = ""
A9 = ""

I want A5, the next empty cell in the range, to hold
the
name Anne, but not the remainder of the empty cells?

I can set up a flag, say in the range C1 to C9, so that
if any cells in the A1 to A9 range are empty the flag
cell shows "0", or if any are not, the

flag cell shows "1", or if the cell above is empty, the
flag cell shows "2", eg

0 = empty, 1 = not empty, 2 = empty but the cell
above
is also empty

C1 = 1 (A1 is not empty)
C2 = 1 (A2 is not empty)
C3 = 1 (A3 is not empty)
C4 = 1 (A4 is not empty)
C5 = 0 (A5 is empty)
C6 = 2 (A6 is empty but so is the previous cell)
C7 = 2 (and so on)
C8 = 2
C9 = 2


This way, I can use the MATCH function to find out that
the next empty cell is in row 5.

But then, if a user inputs into cell D1 the name Anne,
how do I "push" that value from D1 into the next
available empty cell, A5, and not all the

other empty cells, A6 to A9?

I feel I should be using the INDIRECT function somehow,
but I cannot get my head around the problem.

Any guidance would be appreciated.

Er.... I would rather not use VBA if I can avoid it. My
brain is too small to start to learn yet another
programming style, :)

Many thanks,

Mark


.

There are instructions here for manually or programmatically filling
blank cells in a column:

http://www.contextures.com/xlDataEntry02.html

Mark Flaxman wrote:
Hi, how do I "push" a value into a cell?

What do I mean?

I know that if I have cell A1, I can make the contents of
that cell equal to the value of any other cell in a
workbook. The easiest way is to enter

the formula in cell A1, "=B1" (where B1 holds the
contents that I want A1 to hold). I can even make a
conditional formula, eg if A1 is empty, fill it

with the contents of B1, else leave it alone.

But, what if I have a range of cells, eg A1 to A9 that I
want to fill, but only from top down, and only if the
cell is empty and the previous cell in the

range is not? eg;

A1 = Fred
A2 = John
A3 = Mary
A4 = James
A5 = ""
A6 = ""
A7 = ""
A8 = ""
A9 = ""

I want A5, the next empty cell in the range, to hold the
name Anne, but not the remainder of the empty cells?

I can set up a flag, say in the range C1 to C9, so that
if any cells in the A1 to A9 range are empty the flag
cell shows "0", or if any are not, the

flag cell shows "1", or if the cell above is empty, the
flag cell shows "2", eg

0 = empty, 1 = not empty, 2 = empty but the cell above
is also empty

C1 = 1 (A1 is not empty)
C2 = 1 (A2 is not empty)
C3 = 1 (A3 is not empty)
C4 = 1 (A4 is not empty)
C5 = 0 (A5 is empty)
C6 = 2 (A6 is empty but so is the previous cell)
C7 = 2 (and so on)
C8 = 2
C9 = 2


This way, I can use the MATCH function to find out that
the next empty cell is in row 5.

But then, if a user inputs into cell D1 the name Anne,
how do I "push" that value from D1 into the next
available empty cell, A5, and not all the

other empty cells, A6 to A9?

I feel I should be using the INDIRECT function somehow,
but I cannot get my head around the problem.

Any guidance would be appreciated.

Er.... I would rather not use VBA if I can avoid it. My
brain is too small to start to learn yet another
programming style, :)

Many thanks,

Mark


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Yes I think you're right, it is not possible without
using VBA.

I got your Data / Forms method to work. It "was" me who
was wrong. I didn't understand what it was doing, but
Debra's web-site in the replies above these had a link
which pointed me to the right way.

So, I can now enter data in the next empty cell, (or
using a 2D table range, I can input data in the next
empty row), But it isn't quite what I require. I'm almost
there.

I don't trust users to be able to find Data / Forms,
either from the menu or using shortcut keys, and then to
press "New", without making an error and corrupting the
worksheet. I am not belittling the users, it is just that
they are not comfortable with spreadsheets,and I wanted
to keep the process as simple as possible.

I need to create a macro button that, when they are ready
to enter a new record, they press the button, and the
Data / Form dialogue appears, with "New" already selected.

Macro's don't work, because I can't "stop" recording a
macro with the Form dialogue box open.

How else can I do this?

I appreciate all the previous advice. It is getting me
there, :)

I am willing to tackle a VBA solution if there is one, :(

Mark Flaxman

-----Original Message-----
Mark,

I don't think that you can do what you want to do (
append to a range the
value of a *cell* elsewhere on the sheet) other than by
VBA.

The solution I gave (and that the reason why i gave it)
does this, although
the input then is not from a cell, but via a form. I'l
keep an eye on it to
watch if someone else does come with a solution.

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

"Mark Flaxman" wrote in
message
...

Debra,

Many thanks for this. That web page has a link to another
which helped me a lot.

Thanks again

Mark Flaxman

-----Original Message-----
There are instructions here for manually or
programmatically filling
blank cells in a column:

http://www.contextures.com/xlDataEntry02.html


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

.

The following code will open the data form with a new record selected.
Change the sheet name and cell reference to match your worksheet.

'==============================
Sub OpenDataForm()
Dim ws As Worksheet
Set ws = Worksheets("Data")

SendKeys "%w"
ws.Range("A1").Worksheet.ShowDataForm
End Sub
'======================

Mark Flaxman wrote:
Yes I think you're right, it is not possible without
using VBA.

I got your Data / Forms method to work. It "was" me who
was wrong. I didn't understand what it was doing, but
Debra's web-site in the replies above these had a link
which pointed me to the right way.

So, I can now enter data in the next empty cell, (or
using a 2D table range, I can input data in the next
empty row), But it isn't quite what I require. I'm almost
there.

I don't trust users to be able to find Data / Forms,
either from the menu or using shortcut keys, and then to
press "New", without making an error and corrupting the
worksheet. I am not belittling the users, it is just that
they are not comfortable with spreadsheets,and I wanted
to keep the process as simple as possible.

I need to create a macro button that, when they are ready
to enter a new record, they press the button, and the
Data / Form dialogue appears, with "New" already selected.

Macro's don't work, because I can't "stop" recording a
macro with the Form dialogue box open.

How else can I do this?

I appreciate all the previous advice. It is getting me
there, :)

I am willing to tackle a VBA solution if there is one, :(

Mark Flaxman


-----Original Message-----
Mark,

I don't think that you can do what you want to do (

append to a range the

value of a *cell* elsewhere on the sheet) other than by

VBA.

The solution I gave (and that the reason why i gave it)

does this, although

the input then is not from a cell, but via a form. I'l

keep an eye on it to

watch if someone else does come with a solution.

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

"Mark Flaxman" wrote in

message

...



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Debra,

I'm really sorry but I cannot get this to work.

My Input worksheet is named Input. From there the user
will click a button to transport her/him to Sheet1, (a
temporary name for the sheet where the data is to be
stored).

So the macro must be assigned to a button on the first
sheet, (Input), which will open up the second sheet,
(Sheet1), and then open up the data form.

The data range on Sheet1 is A4:H33, (where A4:H4 are
column headers).

My knowledge of VBA is very weak, and the runtime error I
get means nothing to me. I get a runtime error '13' type
mismatch, and debugging points to "Set ws = Worksheets
(Sheet1).

What have I done wrong?

By the way, what is the SendKeys "%w" expression for?

Sorry,

Mark Flaxman


-----Original Message-----
The following code will open the data form with a new
record selected.
Change the sheet name and cell reference to match your
worksheet.

'==============================
Sub OpenDataForm()
Dim ws As Worksheet
Set ws = Worksheets("Data")

SendKeys "%w"
ws.Range("A1").Worksheet.ShowDataForm
End Sub
'======================

Mark Flaxman wrote:

Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

.

Enclose the sheet name in quotation marks --

Set ws = Worksheets("Sheet1")

In the Data Form, the accelerator for the New key is the w. You could
activate it from the keyboard by pressing Alt+w.
Used with the SendKeys statement, % represents the Alt key, so this line
of code is like pressing Alt+w.


wrote:
Debra,

I'm really sorry but I cannot get this to work.

My Input worksheet is named Input. From there the user
will click a button to transport her/him to Sheet1, (a
temporary name for the sheet where the data is to be
stored).

So the macro must be assigned to a button on the first
sheet, (Input), which will open up the second sheet,
(Sheet1), and then open up the data form.

The data range on Sheet1 is A4:H33, (where A4:H4 are
column headers).

My knowledge of VBA is very weak, and the runtime error I
get means nothing to me. I get a runtime error '13' type
mismatch, and debugging points to "Set ws = Worksheets
(Sheet1).

What have I done wrong?

By the way, what is the SendKeys "%w" expression for?

Sorry,

Mark Flaxman



-----Original Message-----
The following code will open the data form with a new

record selected.

Change the sheet name and cell reference to match your

worksheet.

'==============================
Sub OpenDataForm()
Dim ws As Worksheet
Set ws = Worksheets("Data")

SendKeys "%w"
ws.Range("A1").Worksheet.ShowDataForm
End Sub
'======================

Mark Flaxman wrote:



Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

.




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Still not working, I still get the run time error code 13
type mismatch.

And ALT+w just brings up the "Window" menu.

Many thanks all the same. I will have to re-think my
strategy


-----Original Message-----
Enclose the sheet name in quotation marks --

Set ws = Worksheets("Sheet1")

In the Data Form, the accelerator for the New key is the
w. You could
activate it from the keyboard by pressing Alt+w.
Used with the SendKeys statement, % represents the Alt
key, so this line
of code is like pressing Alt+w.


wrote:
Debra,

I'm really sorry but I cannot get this to work.

My Input worksheet is named Input. From there the user
will click a button to transport her/him to Sheet1, (a
temporary name for the sheet where the data is to be
stored).

So the macro must be assigned to a button on the first
sheet, (Input), which will open up the second sheet,
(Sheet1), and then open up the data form.

The data range on Sheet1 is A4:H33, (where A4:H4 are
column headers).

My knowledge of VBA is very weak, and the runtime
error I
get means nothing to me. I get a runtime error '13'
type
mismatch, and debugging points to "Set ws = Worksheets
(Sheet1).

What have I done wrong?

By the way, what is the SendKeys "%w" expression for?

Sorry,

Mark Flaxman



-----Original Message-----
The following code will open the data form with a new

record selected.

Change the sheet name and cell reference to match your

worksheet.

'==============================
Sub OpenDataForm()
Dim ws As Worksheet
Set ws = Worksheets("Data")

SendKeys "%w"
ws.Range("A1").Worksheet.ShowDataForm
End Sub
'======================

Mark Flaxman wrote:



Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

.




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

.

Try naming the data range on sheet1 "Database", using a dynamic formula.
There are instructions he

http://www.contextures.com/xlNames01.html#Dynamic

Your formula should be:

=OFFSET(Sheet1!$A$4,0,0,COUNTA(Sheet1!$A:$A),COUNT A(Sheet1!$4:$4))

If you can't get that to work, and would like to send me a copy, remove
the capital letters from my email address.

Mark Flaxman wrote:
Still not working, I still get the run time error code 13
type mismatch.

And ALT+w just brings up the "Window" menu.

Many thanks all the same. I will have to re-think my
strategy



-----Original Message-----
Enclose the sheet name in quotation marks --

Set ws = Worksheets("Sheet1")

In the Data Form, the accelerator for the New key is the

w. You could

activate it from the keyboard by pressing Alt+w.
Used with the SendKeys statement, % represents the Alt

key, so this line

of code is like pressing Alt+w.


wrote:

Debra,

I'm really sorry but I cannot get this to work.

My Input worksheet is named Input. From there the user
will click a button to transport her/him to Sheet1, (a
temporary name for the sheet where the data is to be
stored).

So the macro must be assigned to a button on the first
sheet, (Input), which will open up the second sheet,
(Sheet1), and then open up the data form.

The data range on Sheet1 is A4:H33, (where A4:H4 are
column headers).

My knowledge of VBA is very weak, and the runtime

error I

get means nothing to me. I get a runtime error '13'

type

mismatch, and debugging points to "Set ws = Worksheets
(Sheet1).

What have I done wrong?

By the way, what is the SendKeys "%w" expression for?

Sorry,

Mark Flaxman




-----Original Message-----
The following code will open the data form with a new

record selected.


Change the sheet name and cell reference to match your

worksheet.


'==============================
Sub OpenDataForm()
Dim ws As Worksheet
Set ws = Worksheets("Data")

SendKeys "%w"
ws.Range("A1").Worksheet.ShowDataForm
End Sub
'======================

Mark Flaxman wrote:




Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

.



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

.




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Mark, Alt+w will only bring up the Window menu if you use it outside of the Data
Form. Debra's suggestion was to use it with sendkeys as part of the routine
*after* the routine has activated the form. If you activate the form manually
using data / Form and then hit Alt+w then you should see it change to new entry.
What is the exact code you are using - Copy and paste it from your module, don't
try and rewrite it in the note ( And just for the record, If Debra tells you it
works, it works, period ;- ).

There is no limit to the number of posts you can make here until you get this
doing exactly what you want it to do, and so far there is no reason why this
should not be a fairly simple exercise, so hang on in there.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Mark Flaxman" wrote in message
...

Still not working, I still get the run time error code 13
type mismatch.

And ALT+w just brings up the "Window" menu.

Many thanks all the same. I will have to re-think my
strategy
<snip


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.576 / Virus Database: 365 - Release Date: 30/01/2004

rotflmao

If you can't get that to work, and would like to send me a copy, remove
the capital letters from my email address.

Debra - Was literally sat here thinking that I would have put money on that
being the next step as it popped in not 2 mins ago. :-)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Debra Dalgleish" wrote in message
...
Try naming the data range on sheet1 "Database", using a dynamic formula.
There are instructions he

http://www.contextures.com/xlNames01.html#Dynamic

Your formula should be:

=OFFSET(Sheet1!$A$4,0,0,COUNTA(Sheet1!$A:$A),COUNT A(Sheet1!$4:$4))

If you can't get that to work, and would like to send me a copy, remove
the capital letters from my email address.

Mark Flaxman wrote:
Still not working, I still get the run time error code 13
type mismatch.

And ALT+w just brings up the "Window" menu.

Many thanks all the same. I will have to re-think my
strategy



-----Original Message-----
Enclose the sheet name in quotation marks --

Set ws = Worksheets("Sheet1")

In the Data Form, the accelerator for the New key is the

w. You could

activate it from the keyboard by pressing Alt+w.
Used with the SendKeys statement, % represents the Alt

key, so this line

of code is like pressing Alt+w.


wrote:

Debra,

I'm really sorry but I cannot get this to work.

My Input worksheet is named Input. From there the user
will click a button to transport her/him to Sheet1, (a
temporary name for the sheet where the data is to be
stored).

So the macro must be assigned to a button on the first
sheet, (Input), which will open up the second sheet,
(Sheet1), and then open up the data form.

The data range on Sheet1 is A4:H33, (where A4:H4 are
column headers).

My knowledge of VBA is very weak, and the runtime

error I

get means nothing to me. I get a runtime error '13'

type

mismatch, and debugging points to "Set ws = Worksheets
(Sheet1).

What have I done wrong?

By the way, what is the SendKeys "%w" expression for?

Sorry,

Mark Flaxman




-----Original Message-----
The following code will open the data form with a new

record selected.


Change the sheet name and cell reference to match your

worksheet.


'==============================
Sub OpenDataForm()
Dim ws As Worksheet
Set ws = Worksheets("Data")

SendKeys "%w"
ws.Range("A1").Worksheet.ShowDataForm
End Sub
'======================

Mark Flaxman wrote:




Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

.



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

.




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.576 / Virus Database: 365 - Release Date: 30/01/2004

Hi Ken,

Thanks for your words m8. I must admit I was anxious that
I was wasting peoples time here, especially Debra's, with
my own lack of ability in understanding a simple VBA
procedure and how to debug it.

I really need to learn VBA before I start asking for VBA
solutions, but I keep putting it off, because of the time
and effort it's going to require.

I have just read Debra's latest reply below this one. I
will have a go at this first and let you both know how I
get on.

Mark

-----Original Message-----
Mark, Alt+w will only bring up the Window menu if you
use it outside of the Data
Form. Debra's suggestion was to use it with sendkeys as
part of the routine
*after* the routine has activated the form. If you
activate the form manually
using data / Form and then hit Alt+w then you should see
it change to new entry.
What is the exact code you are using - Copy and paste it
from your module, don't
try and rewrite it in the note ( And just for the
record, If Debra tells you it
works, it works, period ;- ).

There is no limit to the number of posts you can make
here until you get this
doing exactly what you want it to do, and so far there
is no reason why this
should not be a fairly simple exercise, so hang on in
there.

--
Regards
Ken....................... Microsoft MVP -
Excel
Sys Spec - Win XP Pro / XL 00/02/03

---------------------------------------------------------
-------------------
It's easier to beg forgiveness than ask permission :-)
---------------------------------------------------------
-------------------



"Mark Flaxman" wrote in
message
...

Still not working, I still get the run time error code
13
type mismatch.

And ALT+w just brings up the "Window" menu.

Many thanks all the same. I will have to re-think my
strategy
<snip


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system
(http://www.grisoft.com).
Version: 6.0.576 / Virus Database: 365 - Release Date:
30/01/2004


.

The whole point of the groups is to get you sorted, so until you are sorted we
are not done. :-)

I would take advantage of Debra's offer to look at the file if I were you. If
you were paying for consultancy, it would cost you an arm and a leg for Debra to
do this, but you have the offer for free - Personally I'd use it :-)

Even when Debra has it sorted for you, if you need help in understanding any/all
of the various steps just feel free to post and ask - Folks here tend to get
real helpful indeed when people show a desire to actually understand some of the
solutions posted, and you won't be short of volunteers for explanations. Good
luck.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Mark Flaxman" wrote in message
...

Hi Ken,

Thanks for your words m8. I must admit I was anxious that
I was wasting peoples time here, especially Debra's, with
my own lack of ability in understanding a simple VBA
procedure and how to debug it.

I really need to learn VBA before I start asking for VBA
solutions, but I keep putting it off, because of the time
and effort it's going to require.

I have just read Debra's latest reply below this one. I
will have a go at this first and let you both know how I
get on.

Mark

-----Original Message-----
Mark, Alt+w will only bring up the Window menu if you
use it outside of the Data
Form. Debra's suggestion was to use it with sendkeys as
part of the routine
*after* the routine has activated the form. If you
activate the form manually
using data / Form and then hit Alt+w then you should see
it change to new entry.
What is the exact code you are using - Copy and paste it
from your module, don't
try and rewrite it in the note ( And just for the
record, If Debra tells you it
works, it works, period ;- ).

There is no limit to the number of posts you can make
here until you get this
doing exactly what you want it to do, and so far there
is no reason why this
should not be a fairly simple exercise, so hang on in
there.

--
Regards
Ken....................... Microsoft MVP -
Excel
Sys Spec - Win XP Pro / XL 00/02/03

---------------------------------------------------------
-------------------
It's easier to beg forgiveness than ask permission :-)
---------------------------------------------------------
-------------------



"Mark Flaxman" wrote in
message
...

Still not working, I still get the run time error code
13
type mismatch.

And ALT+w just brings up the "Window" menu.

Many thanks all the same. I will have to re-think my
strategy
<snip


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system
(http://www.grisoft.com).
Version: 6.0.576 / Virus Database: 365 - Release Date:
30/01/2004


.



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.576 / Virus Database: 365 - Release Date: 30/01/2004

Yes, and that's why I suggested that he name the range "Database"

Ken Wright wrote:
Debra, just a thought, but wasn't there an issue in earlier versions where if
the database range did not start on any of the first 2 rows, then it didn't like
it. I seem to remember I had a similar issue with a post recently and am sure
it was one of your posts that put me straight. A possibility perhaps?



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

OK forget that, it was A1:B2 it had to start in, but this was negated by
defining the range with the name 'database' - I'll shut up now :-)

http://support.microsoft.com/default...;en-us;q110462

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Ken Wright" wrote in message
...
Debra, just a thought, but wasn't there an issue in earlier versions where if
the database range did not start on any of the first 2 rows, then it didn't
like
it. I seem to remember I had a similar issue with a post recently and am sure
it was one of your posts that put me straight. A possibility perhaps?

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

<snip


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.576 / Virus Database: 365 - Release Date: 30/01/2004

rotflmao - Found the damn post shortly after and realised exactly that :-(

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Debra Dalgleish" wrote in message
...
Yes, and that's why I suggested that he name the range "Database"

Ken Wright wrote:
Debra, just a thought, but wasn't there an issue in earlier versions where
if
the database range did not start on any of the first 2 rows, then it didn't
like
it. I seem to remember I had a similar issue with a post recently and am
sure
it was one of your posts that put me straight. A possibility perhaps?



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.576 / Virus Database: 365 - Release Date: 30/01/2004

Yippeeeee !!!!!!!!!!!!!!!

It works Debra, many, many thanks, :-))

I can now move on and continue developing the spreadsheet.

I know I really must learn VBA programming. If I had had
a smidgeon of knowledge I would perhaps have picked up
that the last Worksheet term had to remain as Worksheet,
and not the name I have given it.

Again, many thanks to you especially, and to Ken.

(I haven't read the other posts yet. will do that now.)

Mark Flaxman

-----Original Message-----
The line before End Sub should be:

ws.Range("Database").Worksheet.ShowDataForm

And I've tried the code in Excel 97, Excel 2000 and
Excel 2002, and
didn't need to select the Record sheet in order for the
Data Form to
work. The revised code is:

'=================
Sub OpenDataForm()

Dim ws As Worksheet
Set ws = Worksheets("Record")
SendKeys "%w"
ws.Range("Database").Worksheet.ShowDataForm
End Sub
'=====================

LOL

Finishing this thread off with thanks

Mark Flaxman

-----Original Message-----
OK forget that, it was A1:B2 it had to start in, but
this was negated by
defining the range with the name 'database' - I'll shut
up now :-)

http://support.microsoft.com/default.aspx?scid=KB;en-
us;q110462

--
Regards
Ken....................... Microsoft MVP -
Excel
Sys Spec - Win XP Pro / XL 00/02/03

---------------------------------------------------------
-------------------
It's easier to beg forgiveness than ask permission :-)
---------------------------------------------------------
-------------------

Mark,

You're welcome! Thanks for reporting that you've got it working, and
good luck with the rest of the spreadsheet.

Debra

Mark Flaxman wrote:
Yippeeeee !!!!!!!!!!!!!!!

It works Debra, many, many thanks, :-))

I can now move on and continue developing the spreadsheet.

I know I really must learn VBA programming. If I had had
a smidgeon of knowledge I would perhaps have picked up
that the last Worksheet term had to remain as Worksheet,
and not the name I have given it.

Again, many thanks to you especially, and to Ken.

(I haven't read the other posts yet. will do that now.)

Mark Flaxman


-----Original Message-----
The line before End Sub should be:

ws.Range("Database").Worksheet.ShowDataForm

And I've tried the code in Excel 97, Excel 2000 and

Excel 2002, and

didn't need to select the Record sheet in order for the

Data Form to

work. The revised code is:

'=================
Sub OpenDataForm()

Dim ws As Worksheet
Set ws = Worksheets("Record")
SendKeys "%w"
ws.Range("Database").Worksheet.ShowDataForm
End Sub
'=====================





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html