CODEWITHSUNDEEP
excelvba
Hasan
Hasan

Reputation: 31

How to activate a sheet in excel and work in the same sheet without active sheet command?

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

Upvotes: 0

Views: 215

Answers (2)

norie
norie

Reputation: 9857

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

Upvotes: 1

bored.boi
bored.boi

Reputation: 1

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

Upvotes: 0

Related Questions