ACTIVE CELL AND INPUT BOXES

[Jasz74]

Hi all

I'm trying to set up something so that if a user enters "Y" (for yes) in a cell, an input box comes up asking for a profile name and then pastes it into the next cell over (so if the active cell B1 as Y, then it pastes the answer the user enters into C1), then I want another input box to pop up asking for Lineal and then put that answer into 2 columns over from the active cell (so if the active cell is B1 and has Y, then it pastes the answer the user enters into D1)

Thanks

 

[mohadin]

Hi
Put this code in the sheet code

Private Sub Worksheet_Change(ByVal Target As Range)

If UCase(Target) = UCase("Y") Then
Target.Offset(, 1) = InputBox("Profilename")
Target.Offset(, 2) = InputBox("Lineal")
End If
End Sub

 

[Jasz74]

Hi
Put this code in the sheet code

Private Sub Worksheet_Change(ByVal Target As Range)

If UCase(Target) = UCase("Y") Then
Target.Offset(, 1) = InputBox("Profilename")
Target.Offset(, 2) = InputBox("Lineal")
End If
End Sub

That's fab - worked exactly how I wanted!! Thanks so much. I was overcomplicating things lol

 

[mohadin]

You are welcome
And thank you for the feedback
Be happy and safe

 

[Peter_SSs]

That's fab - worked exactly how I wanted!!

The marked solution has been changed accordingly**.
In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.

** I would also suggest/question some changes.

• A small one but UCase("Y") is a wasted function call since "Y" already is upper case.
• As written, when the first input box value is entered to the right, the worksheet change code is needlessly called a second time ... and a third time when the second input box is entered 2 cells to the right. It would be best to disable events while those two values are entered.
• Do you really want this everywhere on the worksheet, or only column B (or at least some restricted columns)?
• Finally if the user does something to multiple cells at once (eg selects two or more cells and hits 'Delete') the code will error.

Perhaps something like this might be 'tighter'?

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.CountLarge = 1 Then
    If Target.Column = 2 And UCase(Target) = "Y" Then
      Application.EnableEvents = False
      Target.Offset(, 1) = InputBox("Profilename")
      Target.Offset(, 2) = InputBox("Lineal")
      Application.EnableEvents = True
    End If
  End If
End Sub

 

[mohadin]

@Peter_SSs

** I would also suggest/question some changes.

Thank you for the Points you mentioned
The marked solution should go to you!!!