4 combo boxes and a userform.

[not very bright dave]

I'm about to enter uncharted waters, at least as far as I'm concerned. I want to build a userform (most likely using comboboxes) which will add a row of data (name in A1, Salary in B1 etc) to a spreadsheet from row 1 down (always using the next available row, then I want to be able to repopulate the userform with a chosen row of data when the row needs modifying. I know it’s a long shot, but is there an easy to follow guide out there anyone can point me in the direction of that sounds similar to this?

I'll be eternally grateful if anyone can help.

Thanks

Dave

 

[HalfAce]

Hi Dave,
Don't know of any guides off hand, but I could show you how this is done fairly easily.
How is the row you want the comboboxes populated with (for editing) to be determined? Will it be for example the active cell row, or are you wanting to be able to select from one of the comboboxes and have the rest of that row populate the remaining comboboxes?

Dan

 

[Tazguy37]

You could use Data, Form (though that won't let you edit records, just delete them and create new ones).

 

[not very bright dave]

Taz, I didn't actually know about that, I'll see if I can make use of that another time.

Dan - I think selecting the record I want to modify from the first field (if possible) in the userform would be fantastic.
Thanks

Dave

 

[HalfAce]

Well,
We can certainly do what I was thinking in the beginning, but Taz's idea would produce the same results, be able to be added to without having to change your userform manually, and can indeed edit or delete your records. It's a very useful tool for what I think you're after. I wish I'da thought of that first myself. Check it out and see if you still want to do everything manually after you've seen what it can do.
(Good call Taz! )

Dan

Edit: You'll just want to make sure you use a header row (in bold is good) for the dataform to recognize the catagories from the records.

 

[HalfAce]

Dave,
Here's a simple example of how your data should look for use in a dataform.
Once you have it layed out like this, simply create a button and assign this code to it. (Amend sheet name or use ActiveSheet)

Sub ShowMyForm()
Sheet1.ShowDataForm
End Sub

Book2
	A	B	C	D	E
1	Name	Salery	Pet1	Pet2
2	Pam	$50,001	Dog	Cat
3	Tom	$50,002	Hog	owl
4	John	$50,003	Pig	Cat
5	Steve	$50,004	Dog	Yak
6	Sammy	$75,000	Cat	Hog
Sheet1

Let me know if you still want to go the way of the userform.
Dan

 

[not very bright dave]

Hi Dan

I looked at Taz's suggestion and it does work. The only problem is that it lets you enter unvalidated data. Ideally, I would like drop down boxes on the fields as I have specific job roles etc the records need to conform to. Can you see an easy solution to this using the dataform?

Thanks

Dave

 

[HalfAce]

No, I don't see a solution to this with the dataform. I think it pretty much allows the users to make any entries they want.

What sort of criteria do you want to use to "validate" the entries in each of your comboboxes? (And you're still thinking of these comboboxes as being in a userform, right?)

Dan

 

[Auriga]

I was really pleased to find this thread as it describes more or less what we are trying to do.
The data sheet works well, but we really need to have option buttons or check boxes for four of the fields as they indicate which of 4 options is being followed up. We also have one field that is for notes and so will have more text entered than the dataform shows.
Is there any way to edit the standard dataform (ie the one from Data > Form)? If not is there any way to copy it and then change the entries I want to amend?

As Dave said in his initial posting, I am entering uncharted waters and I will be eternally grateful if anyone can help!

Thanks.

 

[HalfAce]

Nope. As far as I know there is no way to put optionbuttons / checkboxes on a dataform. You'd need to create a userform for that.
How much text is the notes field likely to have? (I'm thinking a ListBox control in your userform for this)
What exactly would you want the option buttons / checkboxes to do?

"entering uncharted waters"... Does that mean you've not worked with userforms before? If so, they're not hard, there's just a lot of options so you've got some experimenting to do and decisions to make.

If you can describe what it is you want and what you want it to do, we can certainly help.

Dan