Results 1 to 2 of 2

Thread: Excel program

  1. #1
    Join Date
    Jul 2004
    Posts
    1

    Unanswered: Excel program

    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!!

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    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.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •