Adding Select Values to Clipboard: VBA

SAMUSER

Board Regular
Joined
Jul 27, 2012
Messages
82
Hi Everyone,

I have a short script that runs perfectly fine for copying the value from a drop-down list onto the clipboard. However, there are two selections for which I don't want this to happen:

a) When the text selected says "The result has not been chosen"
b) When the text selected says "The result is not available

I see that this would be an if-else statement, with char/string match- but I am not able to code this. If any of you could provide some help, that would be fantastic! Thanks so much!

Code:
Sub copyOutcome()
Set outcomeData = New DataObject
        outcomeData.SetText Range("a16").Value    
        outcomeData.PutInClipboard
End Sub
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Hi SAMUSER,

Here's come code you could try....

Code:
Dim outcomeData As DataObject

Sub copyOutcome2()
    Dim sText As String

    
    sText = Sheets("Sheet1").Range("A16").Value

    
    Select Case sText
        Case "The result has not been chosen", _
            "The result is not available"
            '--do nothing or clear clipboard?

        Case Else
            Set outcomeData = New DataObject
            outcomeData.SetText sText
            outcomeData.PutInClipboard
            Set outcomeData = Nothing
    End Select
End Sub

This could be done with an If ...Then statement also, but I suggested a Select Case block to make it easier for your to add other exceptions in the future.
 
Upvote 0
JS411! Thanks a lot for your help- I will also add message boxes to this once it starts working. But there is a slight error that I am getting:

On the line:

Code:
sText = Sheets("Sheet1").Range("A16").Value

I am getting a subscript out of range error (Run time #9).

Any tips?

Hi SAMUSER,

Here's come code you could try....

Code:
Dim outcomeData As DataObject

Sub copyOutcome2()
    Dim sText As String

    
    sText = Sheets("Sheet1").Range("A16").Value

    
    Select Case sText
        Case "The result has not been chosen", _
            "The result is not available"
            '--do nothing or clear clipboard?

        Case Else
            Set outcomeData = New DataObject
            outcomeData.SetText sText
            outcomeData.PutInClipboard
            Set outcomeData = Nothing
    End Select
End Sub

This could be done with an If ...Then statement also, but I suggested a Select Case block to make it easier for your to add other exceptions in the future.
 
Upvote 0
Modify "Sheet1" to match your actual sheet name.

I added that to your original example because it's best to be explicit and reference the sheet. Otherwise vba will use the ActiveSheet - which might not be the one with the data in A16.
 
Upvote 0
Perfect! Thank you so much! You are a life saver :)

Modify "Sheet1" to match your actual sheet name.

I added that to your original example because it's best to be explicit and reference the sheet. Otherwise vba will use the ActiveSheet - which might not be the one with the data in A16.
 
Upvote 0
Hi JS411! I had one more question (hopefully the last). The code is running, but there is a slight problem. The only results in the drop down menu are now the two "exceptions" that can't be copied. I am not sure how this happened, but the old and new code are shown below:

OLD CODE:

Code:
Sub copyOutcome()
Set outcomeData = New DataObject
        outcomeData.SetText Range("a16").Value
        outcomeData.PutInClipboard
    
End Sub

NEW CODE:

Code:
Sub copyOutcome2()
    Dim sText As String
    sText = Sheets("Selector").Range("A16").Value
    Select Case sText
        Case "No Outcome Indicators for this Desired Outcome", _
            "PM has chosen not to include an Outcomes Indicator"
            Application.CutCopyMode = False
            MsgBox "This selection does not contain an Outcomes Indicator. It should not be transferred to GAIMS, and has not been copied onto the clipboard"
            '--do nothing or clear clipboard?


        Case Else
            Set outcomeData = New DataObject
            outcomeData.SetText sText
            outcomeData.PutInClipboard
            Set outcomeData = Nothing
    End Select
End Sub

Do you have any idea why this may be happening? I would really appreciate the help!

Thanks a lot!
 
Upvote 0
You're right! It is a data validation list. The reference list is correct, but since I edited the macro, the data validation list only shows those two options, and nothing else. It shouldn't affect my other macros, but it seems to be happening...


Is the drop down a Data Validation list in cell a16?


if so check the range address it is referencing.
 
Upvote 0
I figured it out! Seems to be working perfectly fine now (It was a formatting error on my part-sorry!). Thank-you so much for your help JS411!
 
Upvote 0
Is the DV list referencing a named range or a range address?

Changing the macro should not have had an affect on the DV list, but you might have somehow changed the reference around the same time you were editing the macro.

You could try the old macro just to confirm that


EDIT: I see you fixed it. Cheers!
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top