Workbook Macro
in [Programming]
|
Prev: Private Sub Workbook
Next: Geometric progression in VBA
From: Dave Peterson on 7 May 2010 16:05 Do you know what A1 recalculated to that caused the error? There are lots of things/characters/names that can't be used as a sheet name. Slashes for dates is a common problem. terilad wrote: > > Reason for repost was because programming was not working a short time ago. > > Mark > > "Don Guillett" wrote: > > > Pls do NOT repost. See ans in previous thread. GEEZZZZZZZZZZZ > > > > -- > > Don Guillett > > Microsoft MVP Excel > > SalesAid Software > > dguillett(a)gmail.com > > "terilad" <terilad(a)discussions.microsoft.com> wrote in message > > news:B11D556E-9821-470A-B0C5-6C265D3227FB(a)microsoft.com... > > > Hi, > > > > > > How can I write this code to place in the workbook of the file, the macro > > > changes the sheet tab name as per the individual sheet cell A1 which is > > > changed from the first sheet Index of Stock, I am looking to do this so it > > > changes instantly when the name is changed and do not need to calculate > > > each > > > sheet, I have over 100 sheets. > > > > > > Here is the macro > > > > > > Private Sub Worksheet_Calculate() > > > With Me.Range("A1") > > > If .Value <> "" Then > > > Me.Name = .Value > > > End If > > > End With > > > End Sub > > > > > > Many thanks > > > > > > Mark > > > > . > > -- Dave Peterson
From: Project Mangler on 7 May 2010 16:08 Do I read this right - you have a list of sheet names on the first sheet (Index of Stock) which a formula in cell A1 of the target sheet references? What is the trigger for the sheet name to change? What is the structure of Index of Stock? "Project Mangler" <dblack(a)ntlworld.com> wrote in message news:e2VHB7h7KHA.2292(a)TK2MSFTNGP04.phx.gbl... > Works perfectly here. > > > "terilad" <terilad(a)discussions.microsoft.com> wrote in message > news:5541515C-9018-4AF3-BA79-3B0A291BA6CD(a)microsoft.com... > > Reason for repost was because programming was not working a short time > ago. > > > > Mark > > > > "Don Guillett" wrote: > > > > > Pls do NOT repost. See ans in previous thread. GEEZZZZZZZZZZZ > > > > > > -- > > > Don Guillett > > > Microsoft MVP Excel > > > SalesAid Software > > > dguillett(a)gmail.com > > > "terilad" <terilad(a)discussions.microsoft.com> wrote in message > > > news:B11D556E-9821-470A-B0C5-6C265D3227FB(a)microsoft.com... > > > > Hi, > > > > > > > > How can I write this code to place in the workbook of the file, the > macro > > > > changes the sheet tab name as per the individual sheet cell A1 which > is > > > > changed from the first sheet Index of Stock, I am looking to do this > so it > > > > changes instantly when the name is changed and do not need to > calculate > > > > each > > > > sheet, I have over 100 sheets. > > > > > > > > Here is the macro > > > > > > > > Private Sub Worksheet_Calculate() > > > > With Me.Range("A1") > > > > If .Value <> "" Then > > > > Me.Name = .Value > > > > End If > > > > End With > > > > End Sub > > > > > > > > Many thanks > > > > > > > > Mark > > > > > > . > > > > >
From: terilad on 7 May 2010 17:19 Yes you are right, sheet 1 is index of stock that contains 2 columns A3:A53 and B3:B53 containing items that are in stock, when I change one of the items in the columns is changes the name in cell A1 on the individual stock sheet, what I need to do is for the code to change the sheet name tab also so when I click on the item in index of stock it finds the sheet tab with that name and opens that sheet. Mark "Project Mangler" wrote: > Do I read this right - you have a list of sheet names on the first sheet > (Index of Stock) which a formula in cell A1 of the target sheet references? > > What is the trigger for the sheet name to change? What is the structure of > Index of Stock? > > > "Project Mangler" <dblack(a)ntlworld.com> wrote in message > news:e2VHB7h7KHA.2292(a)TK2MSFTNGP04.phx.gbl... > > Works perfectly here. > > > > > > "terilad" <terilad(a)discussions.microsoft.com> wrote in message > > news:5541515C-9018-4AF3-BA79-3B0A291BA6CD(a)microsoft.com... > > > Reason for repost was because programming was not working a short time > > ago. > > > > > > Mark > > > > > > "Don Guillett" wrote: > > > > > > > Pls do NOT repost. See ans in previous thread. GEEZZZZZZZZZZZ > > > > > > > > -- > > > > Don Guillett > > > > Microsoft MVP Excel > > > > SalesAid Software > > > > dguillett(a)gmail.com > > > > "terilad" <terilad(a)discussions.microsoft.com> wrote in message > > > > news:B11D556E-9821-470A-B0C5-6C265D3227FB(a)microsoft.com... > > > > > Hi, > > > > > > > > > > How can I write this code to place in the workbook of the file, the > > macro > > > > > changes the sheet tab name as per the individual sheet cell A1 which > > is > > > > > changed from the first sheet Index of Stock, I am looking to do this > > so it > > > > > changes instantly when the name is changed and do not need to > > calculate > > > > > each > > > > > sheet, I have over 100 sheets. > > > > > > > > > > Here is the macro > > > > > > > > > > Private Sub Worksheet_Calculate() > > > > > With Me.Range("A1") > > > > > If .Value <> "" Then > > > > > Me.Name = .Value > > > > > End If > > > > > End With > > > > > End Sub > > > > > > > > > > Many thanks > > > > > > > > > > Mark > > > > > > > > . > > > > > > > > > > > . >
From: Project Mangler on 7 May 2010 18:01 Hi Mark, Not sure if this will do all that you want: It assumes sheetnames in Col A on the first sheet in the workbook: A double click on the worksheet name in col A should open the sheet. Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean) Dim TgtName As String On Error Resume Next TgtName = Target.Value Sheets(TgtName).Select On Error GoTo 0 End Sub Private Sub Workbook_SheetChange _ (ByVal Sh As Object, ByVal Target As Range) On Error GoTo Errhandler Application.EnableEvents = False If Sh.Name <> Trim("Index of Stock") Then Exit Sub If Target.Column <> 1 Then Application.EnableEvents = True Exit Sub End If If Target.Row - 1 > Sheets.Count Then Application.EnableEvents = True Exit Sub End If Sheets(Target.Row - 1).Name = Target Sheets(Target.Row - 1).Range("A1") = Target Application.EnableEvents = True Errhandler: Application.EnableEvents = True End Sub "terilad" <terilad(a)discussions.microsoft.com> wrote in message news:41B015E4-5185-4D47-A978-B25D33BEEB78(a)microsoft.com... > Yes you are right, sheet 1 is index of stock that contains 2 columns A3:A53 > and B3:B53 containing items that are in stock, when I change one of the items > in the columns is changes the name in cell A1 on the individual stock sheet, > what I need to do is for the code to change the sheet name tab also so when I > click on the item in index of stock it finds the sheet tab with that name and > opens that sheet. > > Mark > > "Project Mangler" wrote: > > > Do I read this right - you have a list of sheet names on the first sheet > > (Index of Stock) which a formula in cell A1 of the target sheet references? > > > > What is the trigger for the sheet name to change? What is the structure of > > Index of Stock? > > > > > > "Project Mangler" <dblack(a)ntlworld.com> wrote in message > > news:e2VHB7h7KHA.2292(a)TK2MSFTNGP04.phx.gbl... > > > Works perfectly here. > > > > > > > > > "terilad" <terilad(a)discussions.microsoft.com> wrote in message > > > news:5541515C-9018-4AF3-BA79-3B0A291BA6CD(a)microsoft.com... > > > > Reason for repost was because programming was not working a short time > > > ago. > > > > > > > > Mark > > > > > > > > "Don Guillett" wrote: > > > > > > > > > Pls do NOT repost. See ans in previous thread. GEEZZZZZZZZZZZ > > > > > > > > > > -- > > > > > Don Guillett > > > > > Microsoft MVP Excel > > > > > SalesAid Software > > > > > dguillett(a)gmail.com > > > > > "terilad" <terilad(a)discussions.microsoft.com> wrote in message > > > > > news:B11D556E-9821-470A-B0C5-6C265D3227FB(a)microsoft.com... > > > > > > Hi, > > > > > > > > > > > > How can I write this code to place in the workbook of the file, the > > > macro > > > > > > changes the sheet tab name as per the individual sheet cell A1 which > > > is > > > > > > changed from the first sheet Index of Stock, I am looking to do this > > > so it > > > > > > changes instantly when the name is changed and do not need to > > > calculate > > > > > > each > > > > > > sheet, I have over 100 sheets. > > > > > > > > > > > > Here is the macro > > > > > > > > > > > > Private Sub Worksheet_Calculate() > > > > > > With Me.Range("A1") > > > > > > If .Value <> "" Then > > > > > > Me.Name = .Value > > > > > > End If > > > > > > End With > > > > > > End Sub > > > > > > > > > > > > Many thanks > > > > > > > > > > > > Mark > > > > > > > > > > . > > > > > > > > > > > > > > > > > . > >
From: Don Guillett on 7 May 2010 18:13
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Application.DisplayAlerts = False Dim WantedSheet As String WantedSheet = Trim(ActiveCell.Value) If WantedSheet = "" Then Exit Sub On Error Resume Next If Sheets(WantedSheet) Is Nothing Then GetWorkbook ' calls another macro to do that Else Application.GoTo Sheets(WantedSheet).Range("a1") End If Application.DisplayAlerts = True End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett(a)gmail.com "terilad" <terilad(a)discussions.microsoft.com> wrote in message news:41B015E4-5185-4D47-A978-B25D33BEEB78(a)microsoft.com... > Yes you are right, sheet 1 is index of stock that contains 2 columns > A3:A53 > and B3:B53 containing items that are in stock, when I change one of the > items > in the columns is changes the name in cell A1 on the individual stock > sheet, > what I need to do is for the code to change the sheet name tab also so > when I > click on the item in index of stock it finds the sheet tab with that name > and > opens that sheet. > > Mark > > "Project Mangler" wrote: > >> Do I read this right - you have a list of sheet names on the first sheet >> (Index of Stock) which a formula in cell A1 of the target sheet >> references? >> >> What is the trigger for the sheet name to change? What is the structure >> of >> Index of Stock? >> >> >> "Project Mangler" <dblack(a)ntlworld.com> wrote in message >> news:e2VHB7h7KHA.2292(a)TK2MSFTNGP04.phx.gbl... >> > Works perfectly here. >> > >> > >> > "terilad" <terilad(a)discussions.microsoft.com> wrote in message >> > news:5541515C-9018-4AF3-BA79-3B0A291BA6CD(a)microsoft.com... >> > > Reason for repost was because programming was not working a short >> > > time >> > ago. >> > > >> > > Mark >> > > >> > > "Don Guillett" wrote: >> > > >> > > > Pls do NOT repost. See ans in previous thread. GEEZZZZZZZZZZZ >> > > > >> > > > -- >> > > > Don Guillett >> > > > Microsoft MVP Excel >> > > > SalesAid Software >> > > > dguillett(a)gmail.com >> > > > "terilad" <terilad(a)discussions.microsoft.com> wrote in message >> > > > news:B11D556E-9821-470A-B0C5-6C265D3227FB(a)microsoft.com... >> > > > > Hi, >> > > > > >> > > > > How can I write this code to place in the workbook of the file, >> > > > > the >> > macro >> > > > > changes the sheet tab name as per the individual sheet cell A1 >> > > > > which >> > is >> > > > > changed from the first sheet Index of Stock, I am looking to do >> > > > > this >> > so it >> > > > > changes instantly when the name is changed and do not need to >> > calculate >> > > > > each >> > > > > sheet, I have over 100 sheets. >> > > > > >> > > > > Here is the macro >> > > > > >> > > > > Private Sub Worksheet_Calculate() >> > > > > With Me.Range("A1") >> > > > > If .Value <> "" Then >> > > > > Me.Name = .Value >> > > > > End If >> > > > > End With >> > > > > End Sub >> > > > > >> > > > > Many thanks >> > > > > >> > > > > Mark >> > > > >> > > > . >> > > > >> > >> > >> >> >> . >> |