click on a cell copy to another sheet

[~Alan]

XL2000
I have a sheet that has pictures of parts and next to the part is a number. On another sheet I have a form
that I need to fill out.
   My question is this is there away to click on the picture and have the  part number associated with the picture
be entered into the  part number cell. or the next empty part number cell
on the form sheet I am using Vlookup to look up the rest of the information ..
I know that I could use a macro for each part number but I see to many problems with that way

 

[Dave Peterson]

If you layout your pictures nicely, I think you can do it.

I'd put the part numbers in the cell adjacent to the topleftcell holding the
picture. (I used the one to the right.)

Then assign each picture this macro or something like it:

Option Explicit
Sub testme()

Dim myPict As Picture
Dim otherWks As Worksheet
Dim destCell As Range

Set otherWks = Worksheets("sheet2")

With otherWks
Set destCell = .Cells(.Rows.Count, "a").End(xlUp).Offset(1, 0)
End With

Set myPict = ActiveSheet.Pictures(Application.Caller)

destCell.Value = myPict.TopLeftCell.Offset(0, 1).Value

MsgBox "Copied!"

End Sub

 

[~Alan]

Thank You very much it works perfectly.
    I needed to change "A" to "DW"  and the form where the part number goes
      I used =DW2. In the next cell down where I needed an other part number I used
                =DW3 and so on now all I need to do is select a small Picture.
 Question If I don't have a picture is it possible to use a similar code in the cell with the part number.

Dave Peterson wrote: If you layout your pictures nicely, I think you can do it.

I'd put the part numbers in the cell adjacent to the topleftcell holding the
picture.  (I used the one to the right.)

Then assign each picture this macro or something like it:

Option Explicit
Sub testme()

    Dim myPict As Picture
    Dim otherWks As Worksheet
    Dim destCell As Range

    Set otherWks = Worksheets("sheet2")

    With otherWks
        Set destCell = .Cells(.Rows.Count, "a").End(xlUp).Offset(1, 0)
    End With

    Set myPict = ActiveSheet.Pictures(Application.Caller)

    destCell.Value = myPict.TopLeftCell.Offset(0, 1).Value

    MsgBox "Copied!"

End Sub

 

[Dave Peterson]

There's not an event that gets fired when you single click on a cell.

You could use rightclick or doubleclicking to do what you want. But I think I'd
put a button from the forms toolbar in Row 1 of that worksheet. Then use
windows|Freeze panes to make sure Row 1 was always visible.

Then you could select the cell you want and copy that activecell's value to the
other sheet.

In fact, you could just use the activecell's row and you won't have to be so
careful on what's selected:

Option Explicit
Sub testme2()

Dim otherWks As Worksheet
Dim destCell As Range

Set otherWks = Worksheets("sheet2")

With otherWks
Set destCell = .Cells(.Rows.Count, "DW").End(xlUp).Offset(1, 0)
End With

With ActiveSheet
destCell.Value = .Cells(ActiveCell.Row, "A").Value
End With

End Sub

Watch out for those columns, again.

 

[~Alan]

I used this in another book over a year ago  and when you select a cell in column A it will enter it in column B
I am not sure what all i did to get it to work
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
   If Target.Column = 1 Then
    If ActiveCell <> "" Then RangeSelection = ActiveCell.Value
    Range(Range("E1")) = ActiveCell.Value
    End If
 End Sub

this is in E1  ="B1"&COUNTA(B:B)+1
 
 

Dave Peterson wrote: There's not an event that gets fired when you single click on a cell.

You could use rightclick or doubleclicking to do what you want.  But I think I'd
put a button from the forms toolbar in Row 1 of that worksheet.  Then use
windows|Freeze panes to make sure Row 1 was always visible.

Then you could select the cell you want and copy that activecell's value to the
other sheet.

In fact, you could just use the activecell's row and you won't have to be so
careful on what's selected:

Option Explicit
Sub testme2()

    Dim otherWks As Worksheet
    Dim destCell As Range

    Set otherWks = Worksheets("sheet2")

    With otherWks
        Set destCell = .Cells(.Rows.Count, "DW").End(xlUp).Offset(1, 0)
    End With

    With ActiveSheet
        destCell.Value = .Cells(ActiveCell.Row, "A").Value
    End With

End Sub

Watch out for those columns, again.

 

[~Alan]

Dave Peterson
I have figured this part out so far
   I started a new subjet "  from Column b to column DW +1  in another sheet "

~Alan wrote: I used this in another book over a year ago  and when you select a cell in column A it will enter it in column B
I am not sure what all i did to get it to work
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
   If Target.Column = 1 Then
    If ActiveCell <> "" Then RangeSelection = ActiveCell.Value
    Range(Range("E1")) = ActiveCell.Value
    End If
 End Sub

this is in E1  ="B1"&COUNTA(B:B)+1
 
 

Dave Peterson wrote: There's not an event that gets fired when you single click on a cell.

You could use rightclick or doubleclicking to do what you want.  But I think I'd
put a button from the forms toolbar in Row 1 of that worksheet.  Then use
windows|Freeze panes to make sure Row 1 was always visible.

Then you could select the cell you want and copy that activecell's value to the
other sheet.

In fact, you could just use the activecell's row and you won't have to be so
careful on what's selected:

Option Explicit
Sub testme2()

    Dim otherWks As Worksheet
    Dim destCell As Range

    Set otherWks = Worksheets("sheet2")

    With otherWks
        Set destCell = .Cells(.Rows.Count, "DW").End(xlUp).Offset(1, 0)
    End With

    With ActiveSheet
        destCell.Value = .Cells(ActiveCell.Row, "A").Value
    End With

End Sub

Watch out for those columns, again.