CODEWITHSUNDEEP
excelvba
Parsnip45
Parsnip45

Reputation: 3

VBA Call a sub and use values from current sub

I'm pretty new to Excel Macros. I'm trying to build a time sheet task tracker where I click an ActiveX Control Button, the Caption of the button writes to Column A, custom input column B and timestamp Column C.

Each individual button gets this code:

Private Sub CommandButton8_Click()
Dim Comments, ProjName As String

Comments = InputBox("Comment")
ProjName = CommandButton8.Caption

Cells(Rows.Count, "A").End(xlUp).Offset(1).Select
ActiveCell.Value = ProjName
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Comments
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Time
ActiveCell.NumberFormat = "h:mm AM/PM"
End Sub

It works, but I know it's very inefficient to copy all of this 8x, particularly when the only thing that changes is "ProjName = CommandButton#.Caption". I think most of it could live in a public sub that I call in the command button, I just can't figure out how to get the public sub to pull the ProjName value from the CommandButton sub. Any suggestions on how to make all of this more efficient in general would be greatly appreciated!

Upvotes: 0

Views: 57

Answers (1)

Tim Williams
Tim Williams

Reputation: 166146

Like this:

Private Sub CommandButton8_Click()
    AddRow CommandButton8.Caption
End Sub

Private Sub CommandButton9_Click()
    AddRow CommandButton9.Caption
End Sub
'etc etc

'common code goes here...
Private Sub AddRow(ProjName As String)
    Dim Comments
    Comments = InputBox("Comment")
    With Activesheet.Cells(Rows.Count, "A").End(xlUp).Offset(1).EntireRow
        .Cells(1).Value = ProjName
        .Cells(2).Value = Comments
        .Cells(3).NumberFormat = "h:mm AM/PM"
        .Cells(3).Value = Time
    End With
End Sub

Note you do not need to Select/Activate cells in order to read/write them.

Upvotes: 1

Related Questions