View Full Version : [SLEEPER:] Grab a Label

Anne Troy
08-17-2004, 07:48 AM
Can we code the attached file?

User places cursor in a row.
Clicks button.
The data is copied to the clipboard in an address format.

I keep seeing this question asked again and again and again...

08-17-2004, 08:17 AM

Where is it supposed to be pasted in?

Kind regards,

Anne Troy
08-17-2004, 08:20 AM
Well, I kind of wanted it just *to the clipboard*, but I guess that's kinda stupid, huh?


Word! (Is there anything else? hee hee)

Anne Troy
08-17-2004, 08:20 AM
Oh. If necessary, we can just go ahead and paste it at the *then current* insertion point in Word.

08-17-2004, 08:54 AM

Something along this line will do what You want:
(I?ve added some checks as well as errorhandling)

'Add this line after You have connected the procedure to the button.
Option Private Module
Option Explicit
Sub GetAddy()
Dim rnData As Range, rnArea As Range, rnSelect As Range, rnCopy As Range
Dim stData As String
Dim vaData As Variant
Dim lnRow As Long, i As Long
On Error GoTo HandleErr
Application.ScreenUpdating = False
Set rnSelect = Selection
Set rnArea = Range("A1:E4")
Set rnCopy = Range("IV1:IV3")
If Not Application.Intersect(rnSelect, rnArea) Is Nothing Then
With rnSelect
If Not .Rows.Count > 1 Then
lnRow = .Row
MsgBox "You should only select one row!", vbInformation
GoTo ExitHere
End If
End With
MsgBox "You must select a cell in the coloured area!", vbInformation
GoTo ExitHere
End If
Set rnData = Range("A" & lnRow & ":" & "E" & lnRow)
vaData = Application.Transpose(rnData.Value)
For i = 1 To 2
rnCopy(i, 1).Value = vaData(i, 1)
Next i
For i = 3 To 5
If i = 5 Then
stData = stData & vaData(i, 1)
stData = stData & vaData(i, 1) & ","
End If
rnCopy(3, 1).Value = stData
'Put it into the clipboard.
MsgBox "You may switch to MS Word and paste it in!", vbInformation
Application.ScreenUpdating = False
Exit Sub
MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "Module1.GetAddy"
Resume ExitHere
End Sub

Kind regards,

Anne Troy
08-17-2004, 01:14 PM
Thanks, Dennis! Swamped with client tasks today. Will check it tonight or tomorrow.


Zack Barresse
08-20-2004, 04:52 PM
... Will check it tonight or tomorrow.

Have you had a chance to check this yet Anne? (I don't want to rush you :) )

Anne Troy
08-20-2004, 05:11 PM
Sorry. Damn clients. Who do they think they are?? Hee hee...
It's finally slowing down a little bit!!
I'll get it this weekend to be sure. I've got a few others too. :)