VBA script to create button

[Sikorsky27]

Okay, I have a script to send me to another sheet and then highlight its importance. But I am looking to add to it for when it goes to said sheet, it will create a back button to go back to the original sheet and deletes the button once used (last part is if at all possible since this will be reused on a total of 48 different but similar buttons). The button should fill whole space on "Assessment" J1. This is the current script

Sub One()
ThisWorkbook.Sheets("Assessment").Visible = True
ThisWorkbook.Sheets("Assessment").Activate
ActiveSheet.Range("A2:I2").Select
ThisWorkbook.Sheets("Q1").Visible = False
End Sub

So after it makes a button and I need to go "back". It should rehide "Assessment", unhide "Q1" and get rid of the button it just placed on the "Assessment" since I have 48 questions and I need the button to be able to go back to each respected question "Q1", "Q2" etc... Thank you!! Just write it if possible, for above script and I will add it to the others.

 

[My Aswer Is This]

I would be very surprised if a Script can be written to create a Button, then add a script to a button have the script do something then go back to another sheet and delete the button

I would use what is referred to as a DoubleClick sheet event script.
No button required to run this type of script.
So, let's say we have a sheet name "Alpha" you double click on any cell in column A and a script is run

We could put the same script in both sheets.
And why do this:
ActiveSheet.Range("A2:I2").Select
Do you just want to select the range and then you will do something manually and then click this button?

And what does this mean:
go back to each respected question "Q1", "Q2" etc
Where will we find "Q1"
Do you mean Range("Q1")

A double click script is very simple to write, and we would only need to put it in the two sheets.

So, what will the script in your buttons look like. Give me the script you would put in these buttons.

 

[My Aswer Is This]

Just noticed this:
You said:
I have a script to send me to another sheet and then highlight its importance
What does it's importance mean?

 

[Sikorsky27]

I would be very surprised if a Script can be written to create a Button, then add a script to a button have the script do something then go back to another sheet and delete the button

I would use what is referred to as a DoubleClick sheet event script.
No button required to run this type of script.
So, let's say we have a sheet name "Alpha" you double click on any cell in column A and a script is run

We could put the same script in both sheets.
And why do this:
ActiveSheet.Range("A2:I2").Select
Do you just want to select the range and then you will do something manually and then click this button?

And what does this mean:
go back to each respected question "Q1", "Q2" etc
Where will we find "Q1"
Do you mean Range("Q1")

A double click script is very simple to write, and we would only need to put it in the two sheets.

So, what will the script in your buttons look like. Give me the script you would put in these buttons.

Sorry you are confused at what I asked help for. Q1 like mentioned previously is a sheet, Sheet "Q1" of "Q48" other sheets. You can create a script that creates a button and auto ties the script to it... the deleting part was what was in question... Again, the button: once you would leave Q1 sheet by my script that I posted... it would bring you to the "Assessment" sheet and it will highlight the areas that pertained on Q1 sheet. But I wanted a button to go back to Q1 (will also be for the other 48 Q sheets I made) from the Assessment sheet.

 

[My Aswer Is This]

This is beyond my knowledgebase.
I will continue to monitor this thread to see what I can learn.

 

[rlv01]

If you restrict yourself to Form Buttons, then it is fairly straightforward to add and delete buttons using VBA.

Simple example.

Sub AddButton()
    Dim MyButton As Button

    'Add a button and assign a macro
    Set MyButton = ActiveSheet.Buttons.Add(250.5, 0.75, 117, 55.5)
    With MyButton
        .Name = "RunOCode"
        .OnAction = "OCode"                           'macro
        .Characters.Text = "Run!"                     'button caption
    End With
End Sub

Sub DeleteButton()
    Dim MyButton As Button

    On Error Resume Next
    Set MyButton = ActiveSheet.Buttons("RunOCode")
    With MyButton
        .Delete
    End With
End Sub

 

[Sikorsky27]

That is awesome, thank you... works just how I want it... I just need to change its locations and size but I will get that... thank you so much, it's perfect

 

[Sikorsky27]

Here is my final which the goes to the other page, creates the button, assigns the desired macro to go back where I want it to go and then the assigned macro deletes the button of the page so the other pages can build one in the same spot. Thank you for helping me, this was the last I needed for this project.

Sub One()
Dim MyButton As Button
Dim rng As Range

ThisWorkbook.Sheets("Assessment").Visible = True
ThisWorkbook.Sheets("Assessment").Activate
ActiveSheet.Range("A2:I2").Select
ThisWorkbook.Sheets("Q1").Visible = False


'Add a button and assign a macro
Set rng = Range("J1")
Set MyButton = ActiveSheet.Buttons.Add(rng.Left, rng.Top, rng.Width, rng.Height)
With MyButton
.Name = "RunBackOne"
.OnAction = "BackOne" 'macro
.Characters.Text = "Back!" 'button caption
End With
End Sub

And to get rid of it:

Sub BackOne()
Dim MyButton As Button

On Error Resume Next
Set MyButton = ActiveSheet.Buttons("RunBackOne")
With MyButton
.Delete
End With
ThisWorkbook.Sheets("Q1").Visible = True
ThisWorkbook.Sheets("Q1").Activate
ThisWorkbook.Sheets("Assessment").Visible = False
End Sub

 

[My Aswer Is This]

Appears as if this only adds one button to a sheet.
And when button is clicked a script runs,
A sheet change event or double click event could do the same thing requiring no button.
Double click on Range("J1") and a script can run activating another sheet and going to a certain range. And any other thing you want. Just a suggestion. Glad you have your add button script working.

Here is a sample of a Double Click script:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Modified  11/13/2022  10:40:34 PM  EST
If Target.Address = "$J$1" Then
Application.Goto Sheets("Q1").Range("A1")
MsgBox "I could do more if wanted"
End If
End Sub

 

[Sikorsky27]

Appears as if this only adds one button to a sheet.
And when button is clicked a script runs,
A sheet change event or double click event could do the same thing requiring no button.
Double click on Range("J1") and a script can run activating another sheet and going to a certain range. And any other thing you want. Just a suggestion. Glad you have your add button script working.

Here is a sample of a Double Click script:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Modified  11/13/2022  10:40:34 PM  EST
If Target.Address = "$J$1" Then
Application.Goto Sheets("Q1").Range("A1")
MsgBox "I could do more if wanted"
End If
End Sub

I will have to look into your double click more... I needed something that was obvious since this is going to other people to use, and it needed to be something that was ease of use but defiantly thank you for your script because I will keep that saved for the future.