CODEWITHSUNDEEP
vbaexcel
user3806255
user3806255

Reputation: 39

How can I make Macro automatical go to next empty row on Excelsheet AND exit all promts with "X"

currently I have a macro named "Test Macro". It adds inputted text to specific cells on my Worksheet which is activated by a button on the worksheet. What I would like is:

(1) Automatically go to the next empty row(line) upon the next entry (button-click). So first button click all inputted text will be row 1 (cells A2-E2). Second button click will add inputted text to row 2(cells A2-E2).

(2) I would like to click the red "x" in the top right corner to close all prompts with out having to go through each Active Range prompt.

Code I have now is:

    Sub TestMacro()
        Range("A2").Select
        ActiveCell.FormulaR1C1 = InputBox("Date")
        Range("B2").Select
        ActiveCell.FormulaR1C1 = InputBox("Project #")
        Range("C2").Select
        ActiveCell.FormulaR1C1 = InputBox("Fault")
        Range("D2").Select
        ActiveCell.FormulaR1C1 = InputBox("Problem")
        Range("E2").Select
        ActiveCell.FormulaR1C1 = InputBox("Solution")
End Sub

Upvotes: 0

Views: 64

Answers (1)

Scott Holtzman
Scott Holtzman

Reputation: 27259

This is one way to fix your problem: 

Option Explicit

Sub TestMacro()

    Dim dateValue As String
    Dim projectNum As String
    Dim fault As String
    Dim problem As String
    Dim solution As String

    dateValue = InputBox("Date")
    If dateValue <> "" Then

        projectNum = InputBox("Project #")
        fault = InputBox("Fault")
        problem = InputBox("Problem")
        solution = InputBox("Solution")

        With Worksheets("mySheet") 'change as needed

            .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0) = dateValue
            .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 1) = projectNum
            .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 2) = fault
            .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 3) = problem
            .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 4) = solution

        End With

    End If

End Sub

BTW, IMO, this design is not very user-friendly. I suggest prompting a user-form one time where users can enter the details and not have to deal with annoying pop-up after pop-up.

Upvotes: 1

Related Questions