0

I am a newbie to Excel VBA. I have written a code with a command button procedure to write some data in another sheet name "EQ_STD_INP" say after clearing the old content in the EQ_STD_INP sheet I want to insert a text in B3 cell I want to know is there any way I could write the program without active sheet command at beginning of each command. Hera is my code.

Private Sub CommandButton2_Click()
    'activate EQ_STD_INP
    Worksheets("EQ_STD_INP").Activate
    'celear cell contents in active sheet
    ActiveSheet.Cells.ClearContents
    'entering joint coordinate command to cell B3
    ActiveSheet.Range("B3").Value = "JOINT COORDINATES"
End Sub
Hasan
  • 31
  • 3
  • Simply write something like `Worksheets("EQ_STD_INP").Cells.ClearContents`, or use a Worksheet variable, or use a `With`- statement. See https://stackoverflow.com/q/10714251/7599798 – FunThomas Apr 19 '21 at 06:47

2 Answers2

1

All you need to do is ensure you have a worksheet reference for each range you are trying to refer to.

You could do that by replacing each occurrence of ActiveSheet with Worksheets("EQ_STD_INP") but you can avoid having to do that using a With statement.

Private Sub CommandButton2_Click()
    ' worksheet reference to EQ_STD_INP
    With Worksheets("EQ_STD_INP")
        .Cells.ClearContents
    'entering joint coordinate command to cell B3
        .Range("B3").Value = "JOINT COORDINATES"
    End With

End Sub
norie
  • 9,609
  • 2
  • 11
  • 18
0
Private Sub CommandButton2_Click()
    
Dim ws As Worksheet
    
    Set ws = Sheets("EQ_STD_INP")
 
    ws.Cells.ClearContents
    
    ws.Range("B3").Value = "JOINT COORDINATES"

End Sub
bored.boi
  • 1
  • 1
    Using a reference variable for the worksheet is fine. You should anyway elaborate a little to explain that to the OP. – Zilog80 May 11 '21 at 18:10
  • 2
    Answer's are great. But for best practices, please provide an explanation. You only posting code makes the OP and future commers copy and paste your answer without understanding the logic behind the answer. Please provide an answer with some explanation. Thank You! – Buddy Bob May 12 '21 at 02:32
  • I will keep those things in mind when answering next time , thanks guys . – bored.boi May 13 '21 at 09:04