New Work Project - Rent Comparability Database

[golf4]

Hi, everyone -

Starting up a new work project voluntarily (scary --- I know). Some
people I've talked to say that Access is the way to go on this one,
but I really feel more comfortable with Excel. So, I'm gonna give it a
shot.

The project includes building a rent comparability database. I work
for a large public housing authority where the requirements state
that, before approving a family to rent a Section 8-assisted unit, we
must evaluate whether the rent, type of unit, unit size, etc... is
comparable to what the proposed Section 8 landlord is proposing to
rent on the program.

I have already taken a number of initial steps: Sheet 1 includes a
userform with about 30 textboxes that I want to use to enter the
existing rent comparability data we have located in a number of
binders. Sheet 2 now has the columns built for each of the 30 items:
Comparaility #, Unit Type, BR size, Rent, Rent Change Date, etc.... I
alo have created 2 command buttons, within the userform, that will
close the userform and one that I would use to load the entered
textbox data into Sheet 2's columns. (GEE, didn't want anyone to think
I'm gonna ask you guys to do EVERYTHING!!!)

SOOOOOO, the first question I would ask is help on VBA code that I can
use is, after I enter the applicable data in all 30 textboxes, how I
would (1) load the data into Sheet 2's rows and (2) have the code
enter the data so that it is loaded row after row moving down Sheet 2?

I do realize my posting is sizable, but I wanted to give as much info
as I could without having my fellow posters nod off....

I really appreciate the continued assistance I get from everyone here
on my projects.

Thanks,

Golf

 

[Cesar Zapata]

If i were you and have access or filemaker around I would try those first.

Regards,
Cesar Zapata

 

[Bob Phillips]

Golf,

It would seem to me that you don't need a userform for managing the data. If
you have structured data, as you seem to suggest, you can use Excel's
built-in Forms.

Just ensure that you have column headings, and goto menu Data>Forms and
voila, a ready built forms application, and automatically mapped to the
worksheet.

By the way, are you a UK Housing Association. If you are, I would be willing
to assist you voluntarily on the project.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[golf4]

Thanks for the responses, guys -

Bob - the reason I'd like to stick with userforms is that I have a
staff that is, somewhat, computer challenged. I already have them
working on an income/rent calculation spreadsheet tool that I've
created and a lot of the data entry is based on "user-friendly"
userforms. I would be concerned that staff would not accept another
tool where they would have to do extra "clicking" to get to data entry
fields. What can I say --- you know about us "lazy" Americans--- .
In addition, future enhancements to my Rent Comparability tool would
be to have "search" userforms to help filter out the types of units
for comparability.

Unfortunately, I'm from the States (Oregon). I certainly wouldn't mind
continuing the communication on the project over the net. I'm not real
sure about the rules and regulations of the UK Housing organizations,
but the project may be to both our advantages. Let me know.

Take care,

Golf

 

[Bob Phillips]

Thanks for the responses, guys -

Bob - the reason I'd like to stick with userforms is that I have a
staff that is, somewhat, computer challenged. I already have them
working on an income/rent calculation spreadsheet tool that I've
created and a lot of the data entry is based on "user-friendly"
userforms. I would be concerned that staff would not accept another
tool where they would have to do extra "clicking" to get to data entry
fields. What can I say --- you know about us "lazy" Americans--- .
In addition, future enhancements to my Rent Comparability tool would
be to have "search" userforms to help filter out the types of units
for comparability.

I'm a (lapsed) mathematician, so don't talk to me about lazy, we invented
it<g>.

Going back to the forms, I would try again to suggest that you look at the
built-in. In your OP you mention adding, but what about deleting, or
amending. The built-in takes care of all of that. You could add event code
that fires the form immediately they enter the data columns, avoiding one
user task.

However, if you insist not, you're the boss on this, so here is some code
for your original request for adding a new entry. I am assuming this is tied
to the 2nd command button, so it is run from the form

'find the first free row
With Sheet2
iLastRow = .Cells(Rows.Count,"A").End(xlUp).Row
.Cells(iLastRow+1,1).Value = Me.Textbox1.Text
.Cells(iLastRow+1,2).Value = Me.Textbox2.Text
.Cells(iLastRow+1,3).Value = Me.Textbox3.Text
'etc.
End With

Unfortunately, I'm from the States (Oregon).

Unfortunately? Oregon is lovely.

I certainly wouldn't mind
continuing the communication on the project over the net. I'm not real
sure about the rules and regulations of the UK Housing organizations,
but the project may be to both our advantages. Let me know.

My offer was not based upon any special Housing Association knowledge, but
rather because they are, at least in the UK, a worthy but under-resourced
facility, so it was in a community spirit sense that I was offering help. I
feel that as a Brit, I should be giving my time for UK based projects, so no
offence made, but I would rather not extend that offer. I will of course
respond to any questions that you post on these NGs if I am able, and you
can mail me direct if you have any particular problems, but I'll stop at
that.


Good luck, and I hope the code snippet helps.