Excel VBA help

I wrote some VBA macros. It has a few UserForms so users could choose their input. I want user to be able to edit some data, like 2rows and 5 columns, if (s)he does not like default values assigned by the program. Right now what I am doing is highlighting that block in an Excel worksheet and then transfering the controls back to Excel. User can change the block, restart again, skipping the editing part. Am I making myself clear?

Is there a better way to do it? I looked at Modeless UserForms but it does not work. One thing I thought was using smartforms as given in ``Definitive guides to Excel VBA'' by Kofler. But the problem is the dimension of the block to be edited is determined by the user's input.

I use Excel 2002.

[NoName]

I don't use user forms, but I did an experiment where I put up a user form and set ShowModal to false, and it did allow me to input data on the spreadsheet while the user form was running. This is with Excel 2000. However, the help says that it the property will be ignored in Excel 97.

Thanks NoName.

What you are suggesting is using modeless userforms. I did try that, it does not work properly when you have more than 1 userform per run.

I did a search on google and foundout some info. I'll post the results if it works the way I want.

[Basso]

Okay, I feel dumb for having to post this, but here goes:

I am trying to get the active cell to move down 1 row. This would be easy, [cells(selection.row + 1, selection.column).select] but I have some hidden rows because I am navigating a filtered list, so it goes to a cell that is filtered out. How do I get it to perform as if I had just hit the "down" arrow on the keyboard?

I am using Excel 2000. Any help would be greatly appreciated!

[NoName]

I don't know if there's a direct way to do it, but you can put the code to increment inside a do loop with the condition "Loop while rows(activecell.row).hidden"

[Brad Gile]

Originally Posted by NoName

I don't know if there's a direct way to do it, but you can put the code to increment inside a do loop with the condition "Loop while rows(activecell.row).hidden"

Exactly. I can't see a better way.

Sub junk()
'1. MOVE DOWN 1 ROW
ActiveCell.Offset(1, 0).Range("a1").Select
'2. IF ACTIVE ROW IS HIDDEN, KEEP MOVING!
Do While Rows(ActiveCell.Row).Hidden
ActiveCell.Offset(1, 0).Range("a1").Select
Loop
End Sub

Brad

[Basso]

Thanks!

Sometimes I miss Lotus 123.

[Brad Gile]

Originally Posted by DogBoy2

Thanks!

Sometimes I miss Lotus 123.

Do you really? I sure don't, and I used it from version 1A (before that I had VisiCalc!) to Excel97. Lotus 123 was a wonderful product, but Lotus dropped the ball completely when Windows 95 came out and Lotus didn't even have a plan for a 32 bit version. Lotus was also revolutionary in having a macro language, but they never developed it beyond a mere toy.

When MS put VBA in Excel, they blew Lotus away forever. That's when I switched to Excel and never looked back.

Brad