Command button code

[Gary]

Hi all,

I have created a command button on a worksheet in an Excel 2003
workbook. I just need to assign some code to this button so that when
clicked, it will simply enter text into a cell in another worksheet in
the same workbook.

Sounds very easy to me, but I've not been able to figure it out yet,
or see any examples.

Can someone help me out please?

Thanks a lot.

Gary.

 

[Norman Jones]

Hi Gary,

Try something like:

'=============>>
Private Sub CommandButton1_Click()
Dim SH As Worksheet
Dim Rng As Range
Const sStr As String = "ABC" '<<==== CHANGE

Set SH = ThisWorkbook.Sheets("Sheet2") '<<==== CHANGE
Set Rng = SH.Range("A1") '<<==== CHANGE
Rng.Value = sStr
End Sub
'<<=============

 

[Gary]

Hi Gary,

Try something like:

'=============>>
Private Sub CommandButton1_Click()
Dim SH As Worksheet
Dim Rng As Range
Const sStr As String = "ABC" '<<==== CHANGE

Set SH = ThisWorkbook.Sheets("Sheet2") '<<==== CHANGE
Set Rng = SH.Range("A1") '<<==== CHANGE
Rng.Value = sStr
End Sub
'<<=============

---
Regards,
Norman











- Show quoted text -

That's great. Thanks a lot.

Using the same button I also need to insert some rows into the same
sheet the button is in, how would that be done?

Thanks again.

 

[Norman Jones]

Hi Gary,

'-----------------
Using the same button I also need to insert some rows into the same
sheet the button is in, how would that be done?
'-----------------

To insert 10 rows try:

'=============>>
Private Sub CommandButton1_Click()
Dim SH As Worksheet
Dim Rng As Range
Const sStr As String = "ABC" '<<==== CHANGE

Set SH = ThisWorkbook.Sheets("Sheet2") '<<==== CHANGE
Set Rng = SH.Range("A1") '<<==== CHANGE
Rng.Value = sStr

SH.Range("A10").Resize(10).EntireRow.Insert
End Sub
'<<=============


Change the value 10 in the expression:

SH.Range("A10").Resize(10).EntireRow.Insert

to amend the number of rows to be inserted.

 

[Gary]

Hi Gary,

Try something like:

'=============>>
Private Sub CommandButton1_Click()
Dim SH As Worksheet
Dim Rng As Range
Const sStr As String = "ABC" '<<==== CHANGE

Set SH = ThisWorkbook.Sheets("Sheet2") '<<==== CHANGE
Set Rng = SH.Range("A1") '<<==== CHANGE
Rng.Value = sStr
End Sub
'<<=============

---
Regards,
Norman











- Show quoted text -

Thanks very much for that. I also need the same button to add a new
row into the worksheet it is located. Can you please help me with
that?

Thanks again.

 

[Norman Jones]

Hi Gary,

To insert the row(s) in the active sheet, change:

SH.Range("A10").Resize(10).EntireRow.Insert

to

Me.Range("A10").Resize(10).EntireRow.Insert

Change the location for the insertion and the number of
rows to be inserted to accord with your requirements.

 

[Norman Jones]

Hi Gary,

'-----------------
I also need the same button to add a new row into the
worksheet it is located. Can you please help me with
that?
'-----------------

Try:

'=============>>
Private Sub CommandButton1_Click()
Dim SH As Worksheet
Dim Rng As Range
Const sStr As String = "ABC" '<<==== CHANGE

Set SH = ThisWorkbook.Sheets("Sheet2") '<<==== CHANGE
Set Rng = SH.Range("A1") '<<==== CHANGE
Rng.Value = sStr

Me.Range("A10").EntireRow.Insert
End Sub
'<<=============

Change the location A10 to suit.