Press Enter to run a Command Button - Driving me CRAZY

[Jimmy110]

Hi All,

Really need your help as I just cant get this to work.... Wanting to type a weight into cell C6, then I have a command button that copies the cell, transfers it to the bottom of a table in a different sheet and then deletes the value from the original cell....

Id love it if after typing the value in C6, all I had to do was press Enter and it would be like pressing the command button.

My code currently looks like this:

Private Sub CommandButton1_Click()
Dim Customername As String, Customerphone As String
Worksheets("sheet1").Select
Customername = Range("C6")
Worksheets("sheet2").Select
Worksheets("sheet2").Range("A1").Select
If Worksheets("sheet2").Range("A1").Offset(1, 0) <> "" Then
Worksheets("sheet2").Range("A1").End(xlDown).Select
End If
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = Customername

Worksheets("sheet1").Select
Worksheets("Sheet1").Range("C6").ClearContents
Worksheets("sheet1").Range("C6").Select



End Sub

Any ideas?

 

[Fluff]

Hi & welcome to MrExcel.
How about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "C6" And Target.Value <> "" Then
      Sheets("Sheet2").Range("A" & Rows.count).End(xlUp).Offset(1).Value = Target.Value
      Target.ClearContents
   End If
End Sub

This needs to go in the Sheet1 code module

 

[Jimmy110]

Hi & welcome to MrExcel.
How about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "C6" And Target.Value <> "" Then
      Sheets("Sheet2").Range("A" & Rows.count).End(xlUp).Offset(1).Value = Target.Value
      Target.ClearContents
   End If
End Sub

This needs to go in the Sheet1 code module

Thanks Fluff....

Doesnt seem to do anything that? Copied it into the blank sheet1 module?

any other ideas?

 

[Fluff]

Is C6 being changed manually & is it on Sheet1?

 

[Jimmy110]

Is C6 being changed manually & is it on Sheet1?

Yeah, C6 is a manual entry at the moment....so for example, someone types in 15.1 and then next to the cell is a command button that does the rest....it would be great if they didn't have to press the command button. Doesn't sound a lot, but slows them down massively

Thanks again

 

[Fluff]

Is C6 a merged cell?

 

[Jimmy110]

Is C6 being changed manually & is it on Sheet1?

sorry...yes its on Sheet1

 

[Jimmy110]

Is C6 a merged cell?

No, its not merged

 

[Fluff]

In that case it should automatically copy across when you change the value of C6.
If you try this, do you get any message boxes

Private Sub Worksheet_Change(ByVal Target As Range)
   MsgBox 1
   If Target.CountLarge > 1 Then Exit Sub
   MsgBox 2
   If Target.Address(0, 0) = "C6" And Target.Value <> "" Then
      MsgBox 3
      Sheets("Sheet2").Range("A" & Rows.count).End(xlUp).Offset(1).Value = Target.Value
      Target.ClearContents
   End If
End Sub

 

[Jimmy110]

In that case it should automatically copy across when you change the value of C6.
If you try this, do you get any message boxes

Private Sub Worksheet_Change(ByVal Target As Range)
   MsgBox 1
   If Target.CountLarge > 1 Then Exit Sub
   MsgBox 2
   If Target.Address(0, 0) = "C6" And Target.Value <> "" Then
      MsgBox 3
      Sheets("Sheet2").Range("A" & Rows.count).End(xlUp).Offset(1).Value = Target.Value
      Target.ClearContents
   End If
End Sub

no, no messages or warnings...just nothing happening.

here's a link to a copy of the sheet Giveaway Calc Copy.xlsm maybe you can make more sense of it?