Using variables in VBA

[madchemist]

I am wondering if someone could help me with this.

I am trying to bring up an input box to ask the user which row they want to do something in.

Here is what I have so far....

Option Explicit

Public CompletionDate As String, Resources As String, Notes As String
'Sub foo()
' Dim s As Shape
' Dim CompletionDate As String, Resources As String, Notes As String
'
' CompletionDate = InputBox("Which row would you like to close")
' 'Range("D19") = CompletionDate
' Resources = InputBox("What date was it closed?")
' 'Range("F19") = Resources
' Notes = InputBox("Who closed it?")
' 'Range("G19") = Notes
'End Sub

Public Sub CloseItem()
Application.Run "InputMe"
Range("P5").Select
ActiveCell.FormulaR1C1 = Resources
Range("Q5").Select
ActiveCell.FormulaR1C1 = Notes
End Sub

Sub InputMe()
CompletionDate = _
InputBox("Which row would you like to close?")
Resources = _
InputBox("What date was it closed?")
Notes = _
InputBox("Who closed it?")
End Sub



How can I change the 5 in "P5" and "Q5" to be whatever row was input? Or is there a way I can make it so that when a user clicks on a cell in a row it runs this macro on that row? I know you can do it with control checkboxes but dont want to go through all the trouble because there are tons and tons of rows to deal with and I do not want to set it all up. Thanks!

 

[Oorang]

instead of specifying the range using explicit Cells (ex "A1") use cell numbers like so:
<hr>
<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> GetInfo()
    <SPAN style="color:#00007F">Dim</SPAN> strCompletionRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> strResources <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> strNotes <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    <SPAN style="color:#00007F">Do</SPAN>
        strCompletionRow = InputBox("Which row would you like to close?", "Enter Row", ActiveCell.Row)
        <SPAN style="color:#00007F">If</SPAN> LenB(strCompletionRow) = 0 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
        <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> IsNumeric(strCompletionRow) <SPAN style="color:#00007F">Then</SPAN> MsgBox "You must enter a numeric value.", vbExclamation, "Invalid Entry"
    <SPAN style="color:#00007F">Loop</SPAN> <SPAN style="color:#00007F">Until</SPAN> IsNumeric(strCompletionRow)
    <SPAN style="color:#00007F">Do</SPAN>
        strResources = InputBox("What date was it closed?", "Enter Date:", Date)
        <SPAN style="color:#00007F">If</SPAN> LenB(strResources) = 0 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
        <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> IsDate(strResources) <SPAN style="color:#00007F">Then</SPAN> MsgBox "You must enter a valid date.", vbExclamation, "Invalid Entry"
    <SPAN style="color:#00007F">Loop</SPAN> <SPAN style="color:#00007F">Until</SPAN> IsDate(strResources)
    strNotes = InputBox("Who closed it?")
    <SPAN style="color:#00007F">If</SPAN> LenB(strNotes) = 0 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    ActiveSheet.Cells(strCompletionRow, 16).Value = strResources
    ActiveSheet.Cells(strCompletionRow, 17).Value = strNotes
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[PolarBear]

If CompletionDate holds the row you want closed, you just need to make these simple changes:

Range("P" & CompletionDate).Select

Range("Q" & CompletionDate).Select