Help with Form function macro

[Peter]

Hi,

I am attempting to create a form which will, when all the boxes have
been completed, will copy the data into a worksheet - to the next
free line e.g. Row 2. Next time the form is used the data will be
copied to the next free line - in this case Row 3.

I am having more than a little trouble working out how to get my VB
macro to identify the row that is free - can anyone please help?


--
Cheers

Peter

Remove the INVALID to reply

 

[Norman Jones]

Hi Peter,

From your description it would seem likely that this functionality could be
provided by the built in Data Form.

With any data or heading cell selected. call the data form:

Data | Form.

You could also look at John Walkenbach's Enhanced Data Form which, as the
name might imply, provides increased functionality, This is available as a
free addin At:

http://www.j-walk.com/ss/dataform

 

[Peter]

Hi Peter,

From your description it would seem likely that this functionality could be
provided by the built in Data Form.

With any data or heading cell selected. call the data form:

Data | Form.

You could also look at John Walkenbach's Enhanced Data Form which, as the
name might imply, provides increased functionality, This is available as a
free addin At:

http://www.j-walk.com/ss/dataform

Hi Norman,

Thanks very much indeed for your reply. I tried what you suggested and
it works OK, but I'm not sure it has the functionality I require.

If I enter a second line it overwrites the first - I need to retain
each entry (I plan, each time I enter the data and it's written to a
line, to send it via email.I also need drop down lists in some of the
cells so i can choose between, say, 5 different entry possibilities -
it looks as though your suggestion of Data | Form may be too limited -
although I could be wrong - it's been known in the past


--
Cheers

Peter

Remove the INVALID to reply

 

[Peter]

Hi Peter,

From your description it would seem likely that this functionality could be
provided by the built in Data Form.

With any data or heading cell selected. call the data form:

Data | Form.

You could also look at John Walkenbach's Enhanced Data Form which, as the
name might imply, provides increased functionality, This is available as a
free addin At:

http://www.j-walk.com/ss/dataform

Hi Norman - I've looked at John;s enhanced data form page - it looks
like it's what i need, but unfortunately I shall be running this on my
work PC and my company don't allow any additions/alterations etc to be
made. hence my need for a macro.



--
Cheers

Peter

Remove the INVALID to reply

 

[Norman Jones]

Hi Peter,

If I enter a second line it overwrites the first

If you click the New button, the data form automatically appends the new
record to the end of the database.

 

[Peter]

Hi Peter,


If you click the New button, the data form automatically appends the new
record to the end of the database.

Thanks Norman, got there eventually! However don't think the Data |
Form is going to suffice.


--
Cheers

Peter

Remove the INVALID to reply

 

[Norman Jones]

Hi Peter,

Hi Norman - I've looked at John;s enhanced data form page - it looks
like it's what i need, but unfortunately I shall be running this on my
work PC and my company don't allow any additions/alterations etc to
be made. hence my need for a macro.

John Walkenbach has put a lot of design time and expertise into his form.
For a relatively modest sum, you can purchase his source code.

 

[Erik BZ]

Try this - it will select the first empty cell in column A

Sub test()
ActiveWorkbook.Sheets(1).Range("A1").End(xlDown).Offset(1, 0).Select
End Sub

 

[Peter]

Hi Peter,


John Walkenbach has put a lot of design time and expertise into his form.
For a relatively modest sum, you can purchase his source code.

I appreciate how much effort John has put in to his excel books etc,
unfortunately I have to run what my company supplies - and it doesn't
include Excel with an un-authorised add-in :-(


--
Cheers

Peter

Remove the INVALID to reply

 

[Bill Kuunders]

Very crude way.............. I'm sure someone will have a more efficient
code but it works for me.

Sub findlast()

Sheets("sort").Select
Range("a4").Select
Application.ScreenUpdating = False
line1:
ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate
If ActiveCell() > 0 Then GoTo line1
ActiveCell.Offset(rowOffset:=0, columnOffset:=0).Activate
Application.ScreenUpdating = True
End Sub

Regards
Bill K