View Full Version : VBA Command
RickGauthier
04-30-2021, 01:14 PM
I have created a Quote Tool with Excel VBA.
when I open it, it is prompting for a project name and then create a sheet with that name from a template.
On the new created sheet, I have a button to go to a sheet called Total.
When I am on the sheet Total, I would like to create a button to go back to new created sheet which has the new project name.
Since this project name changes everytime I open the application, the button has to reference to this new sheet.
How do I create a macro to go back to the said sheet.
Thanks
Rick
jolivanes
05-01-2021, 03:02 PM
Is there a cell in a sheet that has that particular project name?
Or, where is the new sheet placed? At the end? At the beginning? Following a certain sheet?
If the project sheet is added to the end, this should do.
Sub GoTo_Project()
ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count).Select
End Sub
If the project name (same as sheet name) is in Cell B2 of Sheet1.
Sub GoTo_Project()
Dim shName As String
shName = Sheets("Sheet1").Cells(2, 2).Value '<---- Change as required
ThisWorkbook.Sheets(shName).Select
End Sub
In the Template, (soon to be the new Project Sheet,) Code page
Option Explicit
Private Sub Worksheet_Activate()
'Because ProjectName will be erased when Excel Closes
If Me.Name <> "Template" And ProjectName = "" Then ProjectName = Me.Name
End Sub
Sub Button_Click
Sheets("Total").Activate
End Sub
In a Module
Option Explicit
Public ProjectName As String
On the Totals Code page
Option Explicit
Sub Button_Click()
If ProjectName <> "" Then
Sheets(ProjectName).Activate
Else: MsgBox "You must Activate the Project sheet once before this will work"
End If
End Sub
In the Sub that copies the Template to a new sheet
'Set the value of the Public Variable
ProjectName = InputBox("Select Name of Project")
Sheets("Template").Copy Name = ProjectName
All that is just one way to keep the last activated sheet in memory. This version is just for one specific sheet, the one named "ProjectName"
RickGauthier
05-03-2021, 07:40 AM
Is there a cell in a sheet that has that particular project name?
Or, where is the new sheet placed? At the end? At the beginning? Following a certain sheet?
If the project sheet is added to the end, this should do.
Sub GoTo_Project()
ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count).Select
End Sub
If the project name (same as sheet name) is in Cell B2 of Sheet1.
Sub GoTo_Project()
Dim shName As String
shName = Sheets("Sheet1").Cells(2, 2).Value '<---- Change as required
ThisWorkbook.Sheets(shName).Select
End Sub
RickGauthier
05-03-2021, 07:42 AM
Everything under control now.
Thanks to you, I realized what I was doing wrong.
Richard
jolivanes
05-03-2021, 08:18 AM
Thanks for letting us know and good luck
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.