Combo Box Questions

[Guest]

I have created a fill-in form with several combo boxes and some boxes that
you just type data in for example the city name because there is too many to
type in. So I have this nice data entry sheet for several users who will
select their name in a drop down box, then the date (Month, day, year), type
of inspection, etc...etc..what I want it to do is when they finish inputting
the data I want them to be able to hit the SAVE RECORD command button and it
automatically knows what spreadsheet (will be by the inspectors name), to add
a new record to. I would do this in Access, but for some unknown reasoning
this state IT department does not allow the use of Access. So I am stuck
with Excel. Please HELP!

 

[Guest]

Danny:

The solution I'm proposing is based on using the Data->Validation
functionality in Excel, and basing the validation on a list. That way, you
can access the value of the cell in the subroutine. What I have is a
workbook with four sheets:
1) "Inspections" is the sheet the inspectors will use to put in the info
2) The other three sheets, "Moe", "Larry," and "Curley" are the names of
the hypothetical inspectors. The name will go in A6, the inspection date in
B6, and the inspection type in C6. If you have other variables, you can just
follow the pattern I've set up.
3) I then put a command button called "Save Record" below row 6.
4) The command button will be attached to this macro:


Private Sub AddRecord()
Dim strInspectorName As String
Dim dtmInspectionDate As Date
Dim strInspectionType As String
strInspectorName = Range("A6").Formula
dtmInspectionDate = Range("B6").Formula
strInspectionType = Range("C6").Formula
Range("A6").Select
Select Case ActiveCell.Formula
Case Is = "Moe"
ActiveWorkbook.Sheets("Moe").Activate
Case Is = "Larry"
Sheets("Larry").Activate
Case Is = "Curley"
Sheets("Curley").Activate
End Select
Range("A1").Select
Do Until ActiveCell.Formula = ""
ActiveCell.Offset(1, 0).Activate
Loop
ActiveCell.Formula = strInspectorName
ActiveCell.Offset(0, 1).Activate
ActiveCell.Formula = dtmInspectionDate
ActiveCell.Offset(0, 1).Activate
ActiveCell.Formula = strInspectionType
Sheets("Inspections").Activate
Range("A6:C6").ClearContents
End Sub

HTH.

Let me know if I can be of further assistance.

Cheers,
Geoff

 

[Guest]

Thanks I will try it...........I 'am not very use to programming.....so I
will just plug it in an try it out. Thanks again.