Need a macro.

F

famdamly

I need a macro that I can assign to a button that when pressed will cop
the text that is on that button into a cell. I'm stumped, ok well
didn't even try because I know I would be up all night trying then I'
ask anyway, so I'll just ask now
 
N

Nick Hodge

It's difficult to know what 'the text' on the button is but as that is
unlikely to change this is how you would paste a constant text in a cell(s)

Sub PutTextInCell()
Selection.Value="MyText"
End Sub

Places MyText in the selected cell(s)
--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
[email protected]
 
F

famdamly

Thanks for the assistance. The text that I need entered into the cell is
depending on which button I push. The text on the buttons will change
depending on the users own customization. It's sort of my version of a
drop down menu. Someone selects a cell, which is like a field in a
form, they are zinged over to a screenfull of buttons, when they press
one they are zinged back over to the form. Rather than a little drop
down menu, I like to have larger selections to choose from thus
allowing me the possibility of utilizing visual aids. That's just the
way I like to navigate I guess.
 
D

Dave Peterson

Is your form a worksheet designed to look like a form--or a real userform
(designed in the VBE)?

I'm assuming that it's a worksheet.

If you use a button from the forms toolbar, you can assign the same macro to
each button.

I'm not sure how you get from the selected cell to the worksheet with the
button, but this may help:


I put this in a General module:

Option Explicit
Public ActCell As Range
Sub testme()

Dim myBTN As Button

Set myBTN = ActiveSheet.Buttons(Application.Caller)

If ActCell Is Nothing Then
Beep
Else
ActCell.Value = myBTN.Caption
'go back
Application.Goto ActCell
Set ActCell = Nothing
End If

End Sub


I put this behind the worksheet that gets the captions:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("a:a")) Is Nothing Then Exit Sub

Set ActCell = Target
Worksheets("sheetwithbuttons").Activate

End Sub
 
Top