XL 2003 VBA: Code For Executing Macro on ActiveSheet?

[TechTank]

Hi All,

I want to execute a macro on a sheet that will change it's name over time so thought I'd use the ActiveSheet syntax but I'm not sure how to go about it. When I click a Command Button I want this macro to execute on the same sheet and only the sheet that the Command Button is located on but it can't be name dependant if that makes sense?

I've previously had the code for doing this on a sheet that's named in the macro but really just need someone to point me in the right direction.

Thanks everyone and sorry for the noob question but VBA is not the easiest thing to Google it seems .

 

[Paradigm_Shift]

How are you wanting to name the sheet once the button is clicked?

Would there be a userform with a textbox that you would type the name? Would the user select from a dropdown/combobox?

Are you even planning to use a userform at all? Or do you just want the sheet name to change to what ever is, say for example, in A1?

Here is code for a button that would change the sheet name based on the value in the current sheet for A1.

Sub RoundedRectangle1_Click()
    ActiveSheet.Name = Range("A1")
End Sub

 

[TechTank]

Hi, sorry I was a bit vague in my post and thank you for trying to help.

I have this code in another macro and I want to use something similar to look for a value in Column A and then select that cell similar to what the code below does except not insert the row etc. But I need to use the macro on a sheet that will be renamed by other users hence why I need it to work with the active sheet and not be using a sheet by name in the macro itself.

What I am trying to accomplish is to use a Command Button that a user can click and it will take them to a specific row in a worksheet (like hyperlinking to parts in a document. This row will move as users enter data so I am using numerical references to keep track of the rows I want to hyperlink.

Sub Insert_Server()
Dim rng As Range
    
Application.DisplayAlerts = False
Application.ScreenUpdating = False
        
    With Sheets("Environment Information")
        .Unprotect
        Set rng = .Columns("A").Find(What:="1", After:=.Cells(1, 1), _
            LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlPrevious, _
            MatchCase:=False, SearchFormat:=False)
        Sheets("Format Control").Rows(9).Copy
        rng.Offset(1).EntireRow.Insert
        rng.Offset(1, 3).Select
'        .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, _
            AllowInsertingRows:=True, AllowDeletingRows:=True
    End With
    ActiveWindow.ScrollRow = 1

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

Really sorry if that's less than clear but I'm quite new to VBA and Macros.

 

[Paradigm_Shift]

I think I understand what you are wanting.

Try adding something like this to your modules:

Sub CommandButton1_Click()
Dim sheetName As String
sheetName = ActiveSheet.Name
End Sub

This is the basic code. You can manipulate it based on how your sheet is going to be renamed...i.e. if you are linking a cell to the value, etc.

Let me know if this doesn't make any sense and I can explain further.

 

[TechTank]

Thank Paradigm, I'll give it a whirl now :0)

 

[Andrew Poulsom]

If you code is in the module for the worksheet you can refer to the worksheet by using the Me keyword.

 

[TechTank]

I wouldn't know where to start with that bit of information unfortunately Andrew :0(

OK, I think this is sort of what I need and I know this code is rubbish for what I want but it's the nearest thing I have to what I would like to do with help from anyone who may be reading:

Option Explicit
Sub Go_To_Deployment()
Dim rng As Range [B]< Not sure if I need this bit[/B]
        
    With Sheets [B]<this needs="" to="" select="" the="" active="" sheet="" that="" i'm="" currently="" on<="" b=""><this should="" be="" the="" active="" worksheet="" that="" i="" am="" currently="" on
 < This should be the active worksheet that I am currently on
[B]< This bit below is searching for a particular value i.e. 2.1 after Cell A1 but only in column A[/B]
       </this></this>[/B]<this needs="" to="" select="" the="" active="" sheet="" that="" i'm="" currently="" on<="" b=""> Set rng = .Columns("A").Find(What:="2.1", After:=.Cells(1, 1), _
            LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlPrevious, _
            MatchCase:=False, SearchFormat:=False)
        rng.Offset(1, 2).Select</this>[B]<this needs="" to="" select="" the="" active="" sheet="" that="" i'm="" currently="" on<="" b=""> [B]< I need it to find the cell with the value of 2.1 but offset and select the cell that is down 1 row and right 1 cell[/B]

</this>[/B]<this needs="" to="" select="" the="" active="" sheet="" that="" i'm="" currently="" on<="" b="">End Sub

I'm sorry that I seem to be making a bit of a hash with this guys but thank you for your time and patience.</this>

 

[Andrew Poulsom]

You can use Me in place of eg Worksheets("Sheet1") if the procedure is in the module for that worksheet.

 

[TechTank]

Hi Andrew,

You're probably thinking "idiot" but please bear with me. Do you mean replace

With Sheets

with

Me

or

With Me

?

Thank you for your replies though.

 

[Andrew Poulsom]

With Me.