| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
determining a Commndbar name from a cell
This macro is supposed to show a particular Commandbar by getting the
name, previously saved, from cell "E2" in the named spreadsheet. I'm getting an "invalid use of property" error on the marked line. I've tried about 6 different ways of coding this, but none of them have worked. Private Sub Workbook_Activate() Dim strTBarName As String strTBarName = Workbooks("PIP DD Template HideTbar.xls") _ .Worksheets("Macro Records").Range("E2").Value Set cTBar.Name = strTBarName <---ERROR cTBar.Visible = True So I can't set the object's name, only the object. How do I make the object be the one named in the referenced cell ("E2")? Thanks for the help. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
determining a Commndbar name from a cell
Set cTBar = CommandBars(strTBarName)
-- Regards, Tom Ogilvy "davegb" wrote in message ps.com... This macro is supposed to show a particular Commandbar by getting the name, previously saved, from cell "E2" in the named spreadsheet. I'm getting an "invalid use of property" error on the marked line. I've tried about 6 different ways of coding this, but none of them have worked. Private Sub Workbook_Activate() Dim strTBarName As String strTBarName = Workbooks("PIP DD Template HideTbar.xls") _ .Worksheets("Macro Records").Range("E2").Value Set cTBar.Name = strTBarName <---ERROR cTBar.Visible = True So I can't set the object's name, only the object. How do I make the object be the one named in the referenced cell ("E2")? Thanks for the help. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
determining a Commndbar name from a cell
Thanks, Tom. But now I'm getting a variable not set error with that
line. cTBar is publicly declared in another module in this worksheet. Any ideas? Tom Ogilvy wrote: Set cTBar = CommandBars(strTBarName) -- Regards, Tom Ogilvy "davegb" wrote in message ps.com... This macro is supposed to show a particular Commandbar by getting the name, previously saved, from cell "E2" in the named spreadsheet. I'm getting an "invalid use of property" error on the marked line. I've tried about 6 different ways of coding this, but none of them have worked. Private Sub Workbook_Activate() Dim strTBarName As String strTBarName = Workbooks("PIP DD Template HideTbar.xls") _ .Worksheets("Macro Records").Range("E2").Value Set cTBar.Name = strTBarName <---ERROR cTBar.Visible = True So I can't set the object's name, only the object. How do I make the object be the one named in the referenced cell ("E2")? Thanks for the help. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
determining a Commndbar name from a cell
strTBarName = "Worksheet Menu Bar"
set ctBar = CommandBars(strTBarName) ? ctBar.Name Worksheet Menu Bar ? ctBar.controls(1).Caption &File Never heard of a variable not set error. I would get a subscript out of range if strTBarName does not identify a valid Toolbar. -- Regards, Tom Ogilvy "davegb" wrote in message oups.com... Thanks, Tom. But now I'm getting a variable not set error with that line. cTBar is publicly declared in another module in this worksheet. Any ideas? Tom Ogilvy wrote: Set cTBar = CommandBars(strTBarName) -- Regards, Tom Ogilvy "davegb" wrote in message ps.com... This macro is supposed to show a particular Commandbar by getting the name, previously saved, from cell "E2" in the named spreadsheet. I'm getting an "invalid use of property" error on the marked line. I've tried about 6 different ways of coding this, but none of them have worked. Private Sub Workbook_Activate() Dim strTBarName As String strTBarName = Workbooks("PIP DD Template HideTbar.xls") _ .Worksheets("Macro Records").Range("E2").Value Set cTBar.Name = strTBarName <---ERROR cTBar.Visible = True So I can't set the object's name, only the object. How do I make the object be the one named in the referenced cell ("E2")? Thanks for the help. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
determining a Commndbar name from a cell
Tom Ogilvy wrote: strTBarName = "Worksheet Menu Bar" set ctBar = CommandBars(strTBarName) ? ctBar.Name Worksheet Menu Bar ? ctBar.controls(1).Caption &File Never heard of a variable not set error. I would get a subscript out of range if strTBarName does not identify a valid Toolbar. Sorry, was being too brief, meant an "object or withblock variable not set" error. Strangely, this morning, when I ran it, I got a "Syntax error" message. Somehow, when I copied the line of code from here into VBE, the last parenthesis got turned into a curly bracket "}". I fixed it and it runs fine now. -- Regards, Tom Ogilvy "davegb" wrote in message oups.com... Thanks, Tom. But now I'm getting a variable not set error with that line. cTBar is publicly declared in another module in this worksheet. Any ideas? Tom Ogilvy wrote: Set cTBar = CommandBars(strTBarName) -- Regards, Tom Ogilvy "davegb" wrote in message ps.com... This macro is supposed to show a particular Commandbar by getting the name, previously saved, from cell "E2" in the named spreadsheet. I'm getting an "invalid use of property" error on the marked line. I've tried about 6 different ways of coding this, but none of them have worked. Private Sub Workbook_Activate() Dim strTBarName As String strTBarName = Workbooks("PIP DD Template HideTbar.xls") _ .Worksheets("Macro Records").Range("E2").Value Set cTBar.Name = strTBarName <---ERROR cTBar.Visible = True So I can't set the object's name, only the object. How do I make the object be the one named in the referenced cell ("E2")? Thanks for the help. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
determining a Commndbar name from a cell
Tom Ogilvy wrote: strTBarName = "Worksheet Menu Bar" set ctBar = CommandBars(strTBarName) ? ctBar.Name Worksheet Menu Bar ? ctBar.controls(1).Caption &File Forgot to ask in ealier post, what is this code suppose to do? I copied it into VBE, but both the worksheet menu bar line and the & file line went red. So what did you intend I do with this? Never heard of a variable not set error. I would get a subscript out of range if strTBarName does not identify a valid Toolbar. -- Regards, Tom Ogilvy "davegb" wrote in message oups.com... Thanks, Tom. But now I'm getting a variable not set error with that line. cTBar is publicly declared in another module in this worksheet. Any ideas? Tom Ogilvy wrote: Set cTBar = CommandBars(strTBarName) -- Regards, Tom Ogilvy "davegb" wrote in message ps.com... This macro is supposed to show a particular Commandbar by getting the name, previously saved, from cell "E2" in the named spreadsheet. I'm getting an "invalid use of property" error on the marked line. I've tried about 6 different ways of coding this, but none of them have worked. Private Sub Workbook_Activate() Dim strTBarName As String strTBarName = Workbooks("PIP DD Template HideTbar.xls") _ .Worksheets("Macro Records").Range("E2").Value Set cTBar.Name = strTBarName <---ERROR cTBar.Visible = True So I can't set the object's name, only the object. How do I make the object be the one named in the referenced cell ("E2")? Thanks for the help. |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
determining a Commndbar name from a cell
Tom Ogilvy wrote: strTBarName = "Worksheet Menu Bar" set ctBar = CommandBars(strTBarName) ? ctBar.Name Worksheet Menu Bar ? ctBar.controls(1).Caption &File Forgot to ask in ealier post, what is this code suppose to do? I copied it into VBE, but both the worksheet menu bar line and the &file line went red. So what did you intend I do with this? Never heard of a variable not set error. I would get a subscript out of range if strTBarName does not identify a valid Toolbar. -- Regards, Tom Ogilvy "davegb" wrote in message oups.com... Thanks, Tom. But now I'm getting a variable not set error with that line. cTBar is publicly declared in another module in this worksheet. Any ideas? Tom Ogilvy wrote: Set cTBar = CommandBars(strTBarName) -- Regards, Tom Ogilvy "davegb" wrote in message ps.com... This macro is supposed to show a particular Commandbar by getting the name, previously saved, from cell "E2" in the named spreadsheet. I'm getting an "invalid use of property" error on the marked line. I've tried about 6 different ways of coding this, but none of them have worked. Private Sub Workbook_Activate() Dim strTBarName As String strTBarName = Workbooks("PIP DD Template HideTbar.xls") _ .Worksheets("Macro Records").Range("E2").Value Set cTBar.Name = strTBarName <---ERROR cTBar.Visible = True So I can't set the object's name, only the object. How do I make the object be the one named in the referenced cell ("E2")? Thanks for the help. |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
determining a Commndbar name from a cell
You were just supposed to look at it and recognize that the construct of the
code works fine as it was successfully executed from the immediate window. When you recognized that, then you would know that the error is something you are doing which appears to have been the case. -- Regards, Tom Ogilvy "davegb" wrote in message oups.com... Tom Ogilvy wrote: strTBarName = "Worksheet Menu Bar" set ctBar = CommandBars(strTBarName) ? ctBar.Name Worksheet Menu Bar ? ctBar.controls(1).Caption &File Forgot to ask in ealier post, what is this code suppose to do? I copied it into VBE, but both the worksheet menu bar line and the &file line went red. So what did you intend I do with this? Never heard of a variable not set error. I would get a subscript out of range if strTBarName does not identify a valid Toolbar. -- Regards, Tom Ogilvy "davegb" wrote in message oups.com... Thanks, Tom. But now I'm getting a variable not set error with that line. cTBar is publicly declared in another module in this worksheet. Any ideas? Tom Ogilvy wrote: Set cTBar = CommandBars(strTBarName) -- Regards, Tom Ogilvy "davegb" wrote in message ps.com... This macro is supposed to show a particular Commandbar by getting the name, previously saved, from cell "E2" in the named spreadsheet. I'm getting an "invalid use of property" error on the marked line. I've tried about 6 different ways of coding this, but none of them have worked. Private Sub Workbook_Activate() Dim strTBarName As String strTBarName = Workbooks("PIP DD Template HideTbar.xls") _ .Worksheets("Macro Records").Range("E2").Value Set cTBar.Name = strTBarName <---ERROR cTBar.Visible = True So I can't set the object's name, only the object. How do I make the object be the one named in the referenced cell ("E2")? Thanks for the help. |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
determining a Commndbar name from a cell
Tom Ogilvy wrote: You were just supposed to look at it and recognize that the construct of the code works fine as it was successfully executed from the immediate window. When you recognized that, then you would know that the error is something you are doing which appears to have been the case. Thanks. Which points out that I still don't understand how to use the immediate window. -- Regards, Tom Ogilvy "davegb" wrote in message oups.com... Tom Ogilvy wrote: strTBarName = "Worksheet Menu Bar" set ctBar = CommandBars(strTBarName) ? ctBar.Name Worksheet Menu Bar ? ctBar.controls(1).Caption &File Forgot to ask in ealier post, what is this code suppose to do? I copied it into VBE, but both the worksheet menu bar line and the &file line went red. So what did you intend I do with this? Never heard of a variable not set error. I would get a subscript out of range if strTBarName does not identify a valid Toolbar. -- Regards, Tom Ogilvy "davegb" wrote in message oups.com... Thanks, Tom. But now I'm getting a variable not set error with that line. cTBar is publicly declared in another module in this worksheet. Any ideas? Tom Ogilvy wrote: Set cTBar = CommandBars(strTBarName) -- Regards, Tom Ogilvy "davegb" wrote in message ps.com... This macro is supposed to show a particular Commandbar by getting the name, previously saved, from cell "E2" in the named spreadsheet. I'm getting an "invalid use of property" error on the marked line. I've tried about 6 different ways of coding this, but none of them have worked. Private Sub Workbook_Activate() Dim strTBarName As String strTBarName = Workbooks("PIP DD Template HideTbar.xls") _ .Worksheets("Macro Records").Range("E2").Value Set cTBar.Name = strTBarName <---ERROR cTBar.Visible = True So I can't set the object's name, only the object. How do I make the object be the one named in the referenced cell ("E2")? Thanks for the help. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Determining a previous cell value if deleted | Excel Programming | |||
| Determining A Cell If it contain A string value | Excel Programming | |||
| Determining text in a cell | Excel Programming | |||
| Determining active conditional format of one cell | Excel Programming | |||
| Determining whether selected cell has value or formula? | Excel Programming | |||
Linear Mode
