Excel program

canucklehead10 · #1 (**permalink**) 07-23-04, 16:33

Hello,

I was wondering if anyone could help me with the following:

I have no VBA experience, but I want to create a nice spreadsheet that has dialog boxes, etc. Here is what I mean:

I want to have a nice user interface, and then areas for name, date, age, etc...once you click on the dialog box, i want a pop-up that let's you input the data, and then puts it on the spreadsheet in the original area..can this be done?

Some sample code would be wonderful, i'm a stuck at the moment...

Thank you in advance!!

shades · #2 (**permalink**) 07-24-04, 16:20

You might try something like this. I developed a simple input UserForm based on the following code (each column in the worksheet is titled: A:Respondent, B:Team, C:Year, D:Month, E:Project, and this will take the input and palce it into the corresponding columns):

Code:

Sub WSAInput() 
    Dim myResp As String 
    Dim myTeam As String 
    Dim myYear As String 
    Dim myMonth As String 
    Dim myProject As String 
     
    Dim r As Variant 
    Application.ScreenUpdating = False 
     
    myResp = InputBox("Who is respondent?") 
    myTeam = InputBox("Team Member?") 
    myYear = InputBox("Year") 
    myMonth = InputBox("Month") 
    myProject = InputBox("Project Title") 
     
    Sheets("Work").Range("A65536").End(xlUp).Select 
    ActiveCell.Offset(1, 0).FormulaR1C1 = myResp 
    ActiveCell.Offset(1, 0).Range("A1").Select 
    Selection.AutoFill Destination:=ActiveCell.Range("A1:A8"), Type:= _ 
    xlFillDefault 
     
    Sheets("Work").Range("B65536").End(xlUp).Select 
    ActiveCell.Offset(1, 0).FormulaR1C1 = myTeam 
    ActiveCell.Offset(1, 0).Range("A1").Select 
    Selection.AutoFill Destination:=ActiveCell.Range("A1:A8"), Type:= _ 
    xlFillDefault 
     
    Sheets("Work").Range("C65536").End(xlUp).Select 
    ActiveCell.Offset(1, 0).FormulaR1C1 = myYear 
    ActiveCell.Offset(1, 0).Range("A1").Copy 
    ActiveCell.Range("A2:A9").Select 
    ActiveSheet.Paste 
     
    Sheets("Work").Range("D65536").End(xlUp).Select 
    ActiveCell.Offset(1, 0).FormulaR1C1 = myMonth 
    ActiveCell.Offset(1, 0).Range("A1").Copy 
    ActiveCell.Range("A2:A9").Select 
    ActiveSheet.Paste 
     
    Sheets("Work").Range("E65536").End(xlUp).Select 
    ActiveCell.Offset(1, 0).FormulaR1C1 = myProject 
    ActiveCell.Offset(1, 0).Range("A1").Copy 
    ActiveCell.Range("A2:A9").Select 
    ActiveSheet.Paste 
     
    Application.CutCopyMode = False 
     
    Application.ScreenUpdating = True 
     
    Sheets("Work").Range("A65536").End(xlUp).Select 
    ActiveCell.Offset(1, 0).Range("A1").Select 
     
End Sub

You could attach this to a button on the worksheet.

Internet Services

Web Development

Digital Marketing

Consumer Tech