Excel - VBA Macro

[debbiezzzzz]

Hello,

I have to write a macro or vba code and add them as buttons on a worksheet for our users. Unfortunately, I really don't know excell VBA. (I've used Access VBA extensively, and VB, but not sure on the objects methods etc for Excel.)

Anyhow what I want to write is basically this...

The sheet is has about 10 colums of info, and one row for each "record". There are for categories of line items that each have their own counter. The ID for each record follows a naming convention. (AAA-001, AAA-002 etc for type AAA, BBB-001, BBB-002 etc. for type BBB and similar for Types CCC and DDD)

What I want to do is have four buttons. Each to ADD a new line for AAA or BBB or CCC or DDD. The macro will need to find the last AAA or BBB or CCC or DDD record and insert a new blank line, Add the new entry (CCC-004 for example) to column A and leave the cursor on Column B for this new record.

If the user accidently sorts by another column, I would maybe have to sort it by column A again first?

Column A might look like this in a typical scenario.

AAA-001
AAA-002
AAA-003
AAA-004
BBB-001
BBB-002
CCC-001
DDD-001
DDD-002

If the User clicks the (Add C record.) I would want a new line below CCC-001 added, and then Column A filled in with CCC-002 automatically.

Anyhow, if anyone can point me in the right direction to get some sample Excel VBA that might help, that would be great.

Thanks,

D

 

[xlbo]

Please post in forum707


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Please post VBA questions in Forum707.

Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}

 

[xlbo]

too slow Skip ;-)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[debbiezzzzz]

Hmmm, tried posting in that forum 3 times now. I don't receive an error, but it also dosn't add to list...

 

[SkipVought]

BTW, You are making this harder than it needs to be.

"What I want to do is have four buttons. Each to ADD a new line for AAA or BBB or CCC or DDD. The macro will need to find the last AAA or BBB or CCC or DDD record and insert a new blank line, Add the new entry (CCC-004 for example) to column A and leave the cursor on Column B for this new record."

Why? Just add all ALL new rows to the bottom of the table and SORT on column A.

"If the user accidently sorts by another column, I would maybe have to sort it by column A again first?"

Oops? A feature of Excel is that the user can choose to sort whatever way they choose. SO WHAT?

Check out the COUNTIF function for your numbering.


Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}

 

[debbiezzzzz]

Why? Just add all ALL new rows to the bottom of the table and SORT on column A.

My example was showing the basic function. In reality, each of those types have breaks, diffrent formatting, etc...
I figured each button would be better.

 

[SkipVought]

The INSERT method if frought with problems that I advise to avoid.

OK, use 4 buttons. That seems reasonable, given the formatting and numbering issues. But my advice is to avoid INSERT.

Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}