Adjust code to allow multi choice please?

[garyi]

Hi there, a kindly soul here gave me so code which sits within the sheet so nothing needs to be pressed.

In essence if I pick yes from a pop up box then it whisks that row off to another sheet, and this works great.

However I would now like to have Yes/No/Maybe in the pop up and depending on what is chosen determins where the line gets taken.

I tried simply duplicating the code and changing Yes to No, but it said there was ambiguity with the name.

Could someone help me please?

So to conclude, if I select yes it does what it does now. If I select no it goes to sheet called No and if I chose maybe the line goes to a Maybe worksheet.

Thanks for any help.

Private Sub Worksheet_Change(ByVal Target As Range)
 Application.EnableEvents = False
'Was change made to Column D?
  If Target.Column = 10 Then
'If yes, was a Y entered?
   If Target = "Yes" Then
'If Yes, Store Row number, Determine next empty Row in
'Cleared sheet, Move Row, Delete Row
    delRow = Target.Row
      Target = ""
      nxtRow = Sheets("Completed").Range("A" & Rows.Count).End(xlUp).Row + 1
      Target.EntireRow.Cut Destination:=Sheets("Completed").Range("A" & nxtRow)
      Rows(delRow).EntireRow.Delete shift:=xlUp
   End If
  End If
 Application.EnableEvents = True
End Sub

[TMS]

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook. Don't upload a picture when you have a workbook question. None of us is inclined to recreate your data. Upload the workbook and manually add an 'after' situation so that we can see what you expect. In addition clearly explain how you get the results..
To attach a file to your post, you need to be using the main 'New Post' or 'New Thread' page and not 'Quick Reply'.
To use the main 'New Post' page, click the 'Post Reply' button in the relevant thread.

On this page, below the message box, you will find a button labelled 'Manage Attachments'.
Clicking this button will open a new window for uploading attachments.

You can upload an attachment either from your computer or from another URL by using the appropriate box on this page.
Alternatively you can click the Attachment Icon to open this page.

To upload a file from your computer, click the 'Browse' button and locate the file.

To upload a file from another URL, enter the full URL for the file in the second box on this page.
Once you have completed one of the boxes, click 'Upload'.

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[p24leclerc]

As I was not sure how your worksheet was contructed, I modified your macro so it will repond to a change in column J (10) which uses DATA VALIDATION which limits data to be Yes, No or Maybe. There is no popup at all.
When you change the value of a cell in column J, your macro will react based on its value. You first have to create sheet "No" and sheet "Maybe"

Private Sub Worksheet_Change(ByVal Target As Range)
 Application.EnableEvents = False
'Was change made to Column D?
  If Target.Column = 10 Then
'If yes, was a Y entered?
   If Target = "Yes" Then
    'If Yes, Store Row number, Determine next empty Row in
    'Cleared sheet, Move Row, Delete Row
      delRow = Target.Row
      Target = ""
      nxtRow = Sheets("Completed").Range("A" & Rows.Count).End(xlUp).Row + 1
      Target.EntireRow.Cut Destination:=Sheets("Completed").Range("A" & nxtRow)
      Rows(delRow).EntireRow.Delete shift:=xlUp
    ElseIf Target = "No" Then
      delRow = Target.Row
      Target = ""
      nxtRow = Sheets("No").Range("A" & Rows.Count).End(xlUp).Row + 1
      Target.EntireRow.Cut Destination:=Sheets("No").Range("A" & nxtRow)
      Rows(delRow).EntireRow.Delete shift:=xlUp
    
    ElseIf Target = "Maybe" Then
      delRow = Target.Row
      Target = ""
      nxtRow = Sheets("Maybe").Range("A" & Rows.Count).End(xlUp).Row + 1
      Target.EntireRow.Cut Destination:=Sheets("Maybe").Range("A" & nxtRow)
      Rows(delRow).EntireRow.Delete shift:=xlUp
    
   End If
  End If
 Application.EnableEvents = True
End Sub

[garyi]

Hi There I tried this one, I am not getting a debug problem or anything, however nothing has occured.

I have attached my excel sheet if anyone can help.

Just to clarify the dashboard sheet is where I will keep things that are current. Once complete I will pick from the pop up box in column J, as soon as its selected it will get dumped into the win/lose/maybe pile (sorry changed the names)

I hope this makes sense, if there is a more simple way then thats fine.

Thanks for the great suggestions.

[Richard Buttrey]

Hi,

The values in your column 4 (D) have changed and no longer match the macro. I Assume you are now using column 10 (I). In which case alter the macro accordingly.

You have also changed the destination sheets from "Completed", "No" & "Maybe" to "Lose", "Postponed" & Win. In which case you'll need to also change the IF test line.
i.e.

If LCase(Target) = "win" Or LCase(Target) = "postponed" Or LCase(Target) = "lose" Then

Regards

[garyi]

Very odd, it was not working on my macbook but is fine on my main mac. Perhaps macros got disabled or something.

Thats cool then, it all appears to work well.

THanks again for everyones help.

[garyi]

Hello everyone, sadly the sheet is not working correctly, when a line is moved to another page it simply copies over the last one, so there is only every one line on 'win', 'lose' etc. I wanted to to drop it into the next line.

To my untrained eye the macro should do it but its not?

Sorry to be a pain, I have attached my sheet

[BarryTSL]

I added just 2 more if statements to see your selection and used aaa as a variable to select the correct sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
 Application.EnableEvents = False
'Was change made to Column D?
  If Target.Column = 10 Then
'If yes, was a Y entered?
   If Target = "Yes" Then aaa = "Completed"
   If Target = "No" Then aaa = "No"
   If Target = "Maybe" Then aaa = "Maybe"
'If Yes, Store Row number, Determine next empty Row in
'Cleared sheet, Move Row, Delete Row
    delRow = Target.Row
      Target = ""
      nxtRow = Sheets(aaa).Range("A" & Rows.Count).End(xlUp).Row + 1
      Target.EntireRow.Cut Destination:=Sheets(aaa).Range("A" & nxtRow)
      Rows(delRow).EntireRow.Delete shift:=xlUp
   End If
 Application.EnableEvents = True
End Sub

you can change the code to suit the sheet names you prefer.

[Richard Buttrey]

Hi,

Was that a typo in your code where the change to be detected is in column 4 (D) but the macro refers to column 10?
I assume it is column 4.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim delRow As Long, nxtRow As Long, stSheet As String
    Application.EnableEvents = False
    'Was change made to Column D?
    If Target.Column = 4 Then
        stSheet = Target
        If LCase(stSheet) = "yes" Then stSheet = "Completed"
        'If yes, was a Y entered?
        If LCase(Target) = "yes" Or LCase(Target) = "no" Or LCase(Target) = "maybe" Then
            'If Yes, Store Row number, Determine next empty Row in
            'Cleared sheet, Move Row, Delete Row
            delRow = Target.Row
            nxtRow = Sheets(stSheet).Range("A" & Rows.Count).End(xlUp).Row + 1
            Target.EntireRow.Cut Destination:=Sheets(stSheet).Range("A" & nxtRow)
            Rows(delRow).EntireRow.Delete shift:=xlUp
        End If
    End If
    Application.EnableEvents = True
End Sub

[garyi]

Yea well spotted, I had inserted an extra column and made some changes.

I will give the suggestions a try thanks for everyones help.

[p24leclerc]

I just tried your sheet and it seems to be working well. As soon as you change the cell in column J to Win/Loose/... it moves this line to the appropriat sheet.
As your data starts at column B, you have to change the program to look at the nxtRow in column B and not in column A as it used to be. Otherwise, all lines are copied to the same line in the sheets.

Except for this glitch, it works.

[garyi]

Its OK I worked it out