Solved How to code in VBA

Microsoft Excel 2010 - complete product...
August 30, 2015 at 17:52:48
Specs: Windows 7
New to VBA. I am trying to code in VBA an automatic function.

If I select "Yes" from a validated cell: Worksheets("Checklist").Range("H21", "R78")
I would like to automatically create an Input Box to insert several sections of data. Project Description, Project Value, Start Date.
This information is to be inserted into another Worksheet on the next available row, in Columns J, K & L.

See More: How to code in VBA

Report •

August 31, 2015 at 04:02:25
I'm a bit confused.

You mentioned a "validated cell" (single) then you included an reference to a Range of 2 cells, H21 and R78.

Please explain.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

August 31, 2015 at 04:22:28
Hi DerbyDad03,

Thank you for taking the time to reply.

Minor correction.
If I select "Yes" from a validated cell: Worksheets("Checklist").Range("M21", "N78")
. In each row from 21 down to 78, I have combined the cells of Column M & N
. I have assigned Data Validation to each of these combined cells, where only a 'Yes' or 'No' can be entered.
. If 'Yes' is entered into the particular cell, I want to create an InputBox...

message edited by ChrisBrook

Report •

August 31, 2015 at 07:31:03
✔ Best Answer
re: In each row from 21 down to 78, I have combined the cells of Column M & N

Just FYI...when cells are merged, VBA (and Excel) typically sees the merged range as a single cell. In other words, with M21:N21 merged, the value is actually stored in M21 and N21 is empty. Therefore, the code only needs to check M21:M78 for a "Yes".

The following code seems to accomplish what you've asked for.

As written, it is very basic. I have not assigned a Type to the InputBoxes, which is something you might want to consider. By using the Type argument, you can ensure that a user only enters Text in the proj_Desc InputBox, a Date in the strt_Date InputBox, etc. Since I don't know the format of your inputs, I didn't include a Type.

You may also want to include code to ensure that things like the Dates are valid. In other words, you may want to ensure that a user can't enter a date from the Jurassic time period as a Start Date. ;-) There are a number of things you might want to add to make the code more user-proof, but I hope that this will get you started.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim proj_Desc, proj_Val, strt_Date, nxt_Rw

'Determine if change was made to a singe cell in
'the desired range and if the choice was Yes
  If Target.Cells.Count = 1 And _
    Not Intersect(Target, Range("M21:M78")) Is Nothing And _
     Target = "Yes" Then

'Get input from user, Exit if cancelled
   proj_Desc = Application.InputBox("Enter Project Description", "Project Description")
    If proj_Desc = False Then Exit Sub
   proj_Val = Application.InputBox("Enter Project Value", "Project Value")
    If proj_Val = False Then Exit Sub
   strt_Date = Application.InputBox("Enter Start Date", "Start Date")
    If strt_Date = False Then Exit Sub

'Determine next Available Row in Sheet2
      nxt_Rw = Sheets(2).Range("J" & Rows.Count).End(xlUp).Row + 1

'Place input in Columns J:L
        Sheets(2).Range("J" & nxt_Rw) = proj_Desc
        Sheets(2).Range("K" & nxt_Rw) = proj_Val
        Sheets(2).Range("L" & nxt_Rw) = strt_Date
  End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Related Solutions

Ask Question