Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!

Vba Question: How To Rename Sheets With A Keyword In Them?

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

I'm running a macro to rename sheets within a book and I'm currently using the 'for each ws in worksheets' command. This isn't really correct though as I'd like to only rename sheets which are named Node X (As in Node 1, Node 2, etc). So I guess the easiest thing would be to put as the first item in the loop: if = "Node " & X then

The trouble is I'm not sure how to do the X part! Any pointers?! Thanks in advance!

View Answers     

Similar Excel Tutorials

Rename a Module for an Excel Macro
This Excel tip shows you how to rename a module in Excel. This is a very important thing to do when you have a larg ...
Change the Name of a Chart in Excel
How to change the name of a chart in Excel. This allows you to use a more intuitive chart reference when organizin ...
Sort Worksheet Tabs - Ascending or Descending Order
This macro will sort all of the worksheets in the current workbook. It can sort in ascending or descending order. ...
Delete All Chart Sheets in Excel - Only Chart Sheets are Deleted - Not Embedded Charts
Delete all chart sheets and tabs in Excel using this macro. This will only delete the charts and graphs that are i ...

Helpful Excel Macros

Name Worksheets Based on Cell Contents
- This macro allows you to have your worksheets named by whatever is in a particular cell within a worksheet. This means
Excel Macro that Searches Entire Workbook and Returns All Matches
- This is the ultimate Lookup Macro for Excel. It will search every worksheet in the workbook and return all of the mat
Delete All Chart Sheets in Excel - Only Chart Sheets are Deleted - Not Embedded Charts
- Delete all chart sheets and tabs in Excel using this macro. This will only delete the charts and graphs that are in the
Delete Empty or 'Broken' Named Ranges (#REF!)
- This macro will delete named ranges which refer to empty or #REF! ranges. This means that if a named range has been 'br
List All Named Ranges in Excel - Displays the Name and Value for Every Named Range Within the Active Workbook in Excel
- List all of the named ranges in a workbook in Excel and the corresponding values stored within every named range. This

Similar Topics

Would like to use XL to create a connected graph ( finance calls it a
binomial lattice). X axis iis the time period t=1,2,3,...T, Y is value.

XY scatter plots points OK. Need to connect points (nodes)--each node except
for t=T has two lines coming out of it.
A 3-peiod example is shown below. Eventually need to generalilze.

Data is
reference T Value
NOde 1... 0 100
NODE 2... 1 110
NODE 3... 1 90
NODE 4... 2 121
NODE 5... 2 100
NODE 6... 2 81

want a line to connect NODE 1 to NODE 2
want a line to connect NODE 1 to NODE 3
want a line to connect NODE 2 to NODE 4
want a line to connect NODE 2 to NODE 5
want a line to connect NODE 3 to NODE 5
want a line to connect NODE 3 to NODE 6

Suggestions would be greatly appreciated.


I want a button that can rename worksheets based on their name. I am thinking it would need a string compare but I'm not sure how to do that.
The button should rename anything named "Node (then a number)" or "Sheet(then a number)" and then rename them to "Node (then a number)" based on the order they're placed in the workbook.

hi all,

i got stuck in a situation which led me to come here for help.
i have an excel sheet which contains node connected each other and i have to draw it either on excel or visio.

User label node A node B
a-b a b
b-c b c
c-d c d
d-b d b

pls help as data is very large.


Is it possible to declare n variable names dynamically in a macro? For example: Cell A2 in Sheet1 contains the number of variables to be considered in the macro (n). I would like all the variables to take names in the macro from node 1 to node n using the dim statement. I tried running a for loop:


for i = 1 to n
dim node&i as BNode ' this is a Netica specific object
end for

however VB doesn't seem to like my node&i

Can you help?



I need a row number to automatically increment as I copy a formula horizontally across columns. Sample spreadsheet downloadable he

Or viewable as a Google Doc he

To elaborate, the formula in question starts in Column T. In T2, the formula compares Node 1 to itself - hence the zero. In T2, it compares Node 1 to Node 2, in T3, Node 1 to Node 3, and so on. Column U does the same thing, except it compares Node 2 to Node 1, Node 2 to Node 2, Node 2 to Node 3, and so on. I want to copy out my formula so that all 50 nodes in the matrix are filled in, in this manner.

The formula increments properly when copied vertically. Take a look at the representative portion of the formula right after the ABS in cell T2: ($C$2-$C2). The formula stays "anchored" on Node 1 via the $C$2. The second part, $C2, increments as you copy it down, to $C3, $C4 and so on. (The same is true for the rest of the formula - it just goes ($D $2-$D2), ($E$2-$E2) and so on.)

Now take a look at Column U. The "anchor" changes from $C$2 to $C$3. That's because Node 2 is now the basis for comparison. In Column V, the anchor changes to $C$4, because Node 3 is the basis for comparison.

The thing is, the anchors in Columns U and V were updated by hand. That is to say, if you copy the formula in Column T one cell to the right, nothing increments. I realize that this is "proper" Excel behavior - when you copy formulas horizontally, generally only the column will increment. But I would really love a way to force the row number of the anchor to automatically increment as I copy the formula to the right. (Note that the anchor has to retain the $ before the row number because when I copy the formula down, that portion of the formula must stay fixed.)

Please let me know if you need any further clarifications. And if you have any suggestions, I would be very grateful. Thank you.


I am trying to create a macros which will tell me which nodes I have to join together with a line in a irregular mesh (Lines need to join nodes vertically and horizontally). For example here is a typical mesh:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152

So for example (for horizontal lines) ,

-ex. node 1 is joined to node 2
-ex. Node 7 os joined to node 8

The output I desire would be in two colums, with the starting node in one column and the corresponding node in the next column.

-ex 1 2
2 3
3 4

I have written a macros, but it doesnt work, and I can't figure out why, see below,

Sub Nodes()
J = 0
For x = 1 To ((Worksheets("Sheet1").Cells(2, 1).Value) - 1)

If Worksheets("Sheet1").Cells(22, x) "" Then

Worksheets("Sheet1").Cells((J + 39), 1).Value = Worksheets("Sheet1").Cells(22, x)



x = x + 1

Loop Until (Worksheets("Sheet1").Cells(22, x).Value) = ((Worksheets("Sheet1").Cells((J + 39), 1).Value) + 1)

Worksheets("Sheet1").Cells((J + 39), 1).Value = Worksheets("Sheet1").Cells(22, x)

End If

J = J + 1

Next x
End Sub

Thanks in advance for any help!!


I want to set the parent nodes to the equivalent of CheckBox1.Value = Null if >0 and < All Child nodes are selected.

The syntax for changing the value of individual nodes seems to be NodeObject.Checked = True but I cannot find a way to set a third (greyed out) state.
Here is some of the code:


Private Sub FlagKeyWordsTreeView_NodeCheck(ByVal Node As MSComctlLib.Node)
Dim CKIDS As Integer
Dim I As Integer
Dim AI As Integer
Dim BI As Integer
Dim X As Integer
    CKIDS = 0
    If Node.Children = 0 Then
    '    If Node.Checked = False Then Node.Parent.Checked = False
        I = Node.Parent.Children
        Set A = FlagKeyWordsTreeView.Nodes(Node.Index).FirstSibling
        Set B = FlagKeyWordsTreeView.Nodes(Node.Index).LastSibling
        AI = A.Index
        BI = B.Index
        For X = AI To BI
            If FlagKeyWordsTreeView.Nodes(X).Checked = True Then
                CKIDS = CKIDS + 1
                If CKIDS = I Then
                    Node.Parent.Checked = True
                ElseIf CKIDS > 1 Then
                    Node.Parent.Checked = False         ' want to put NULL here
                    Node.Parent.Checked = False
                End If
            End If
        Next X
        Set A = FlagKeyWordsTreeView.Nodes(Node.Index + 1).FirstSibling
        Set B = FlagKeyWordsTreeView.Nodes(Node.Index + 1).LastSibling
        AI = A.Index
        BI = B.Index
        For X = AI To BI
           FlagKeyWordsTreeView.Nodes(X).Checked = Node.Checked
        Next X
    End If
End Sub

Hi All,

i was wonderig if anyone knows how to count the number of parents of a treeview node?

I 'm trying to work out what "Level" each node is at i.e. if there are 3 parents to a node it is at level 4 and so on?

Hope that's clear guys

any help is greatly appreciated and i'll defo add to your rep


I have a spreadsheet with about 10 org charts on it. I created these via Insert=>Diagram=>Organizational Chart.

On each sheet I also have a list of positions. I would now like to create some VBA code to enable me to do the following.

When clicking on a cell in the list VBA inserts a node just below the master node with the cell content being linked to the inserted node.
Unfortunately I cannot even seem to get VBA to insert the node let alone fill the text in.

Can anybody out there help at all?

Hi all, Im reviewing some macros and user forms within a spreadsheet, and attempting to work out how the original author has created some functionality

A number of items are defined as nodes. i.e. "Dim strWhatever As node"

When attempting to extract the forms to another sheet, and run code to set them up, I get told "User-defined type not defined", which I assume means that "node" is defined somewhere within the existing VBA, but I can't find it in the existing file

Some code that is falling over is as follows:

Private Sub NodeCheck(ByVal node As MSComctlLib.node)
Call doCheckChildren(node)
Call doCheckParent(node)
End Sub

When checking, the very first line is highlighted

Does anyone have any idea what this means?!

Hello, I hv been encountering this problem whenever i assigned the variable "Node" where the system prompted "User-defined object not defined".

Do I need to manually create the Node object in the object library? or Can point me out how to solve this. Thanks in advance.


Is there a way to get innerText from all childnodes of a node quickly? I am using VBA.

A web page has a node "H1" which has several childnodes, some of them are "p" and some are "STRONG". I need to get the text in these nodes. I tried to use getelementsbytagname and getattribute methods, but it is getting too complex. Is there an easy way?



Hi All,

I have a code which helps me to downloads only the files with specific extensions like xls, xlsx, ppt etc from outlook, Earlier it use to download all attachments but still I have following issues which are still outstanding and I need help for fixing this issues.

1) Download the files from multiple subfolders .i.e. select the folder in tree view and use that selection in main macro to download attachments.
2) Download only the latest files.

I have selected the true for checkboxes in the property window of treeview1 so that we can select the multiple folders.

I also got the below code to get the selected folders of outlook as selection for downloading the attachments from them. I need help to incorporate this so that we can fix the issue no.1


Private Sub TreeView1_NodeCheck(ByVal Node As MSComctlLib.Node) 
    Dim n As Node 
    If Node.Parent Is Nothing Then 
        Set n = Node.Child 
        Do Until n Is Nothing 
            n.Checked = Node.Checked 
            Set n = n.Next 
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines

I am attaching my updated macro file for your reference. please have a look.

Thanks a lot for your help in advance.

Hi all,

I have a computer program here which chucks out thousands of lines of data in a report text file, and I need to extract some data from it.

If I copy and paste the relevant bit, I get lots of lines similar to this one (where the text is in one single cell in each row):


   128     node  dist = 0 elev = -597 temp = 13.7

Basically what I would like to do, is write a macro that can scan through all of the lines in a loop, and if the line contains the phrase "node dist =" then I would like it to extract 3 numbers and deposit them in three separate columns the numbers are "Node Distance", "Elevation", and "Temperature", which in this case are "0", "-597" and "13.7".

If the line contains the phrase "node dist", then the format of the line will always be the same (the number at the beginning (128) is just the line number). So I guess the macro would need to look for each number (including any - signs) after an "=". Is this possible? If so, how on earth do I go about trying to do it??

Thanks in advance!


I'm trying to write to a worksheet and I'm not sure how to make the code position the active cell one cell above the "NODE"(defined name), insert a new row, and then write the data from the userform to the appropriate columns in that new row.
So far, I've got this - it works but always writes the userform values one cell BELOW the "NODE"!!!


 RowCount = Worksheets("Sheet1").Range("NODE").CurrentRegion.Rows.Count
          With Worksheets("Sheet1").Range("NODE")
               .Offset(RowCount, 0).Value = Me.txtFirstName.Value
               .Offset(RowCount, 1).Value = Me.txtLastName.Value
               .Offset(RowCount, 2).Value = Me.cboDepartment.Value
               .Offset(RowCount, 3).Value = DateValue(Me.txtDate.Value)
               .Offset(RowCount, 4).Value = Me.txtAmount.Value
               .Offset(RowCount, 5).Value = Me.txtDescription.Value 
          End With 

Any suggestions on how to select the "NODE", go up one row in the same column, and then write the data?

Thank you!


I'm at a loss figuring out how to set the color of a specific node within a
shape created from BuildFreeform. It's been converted to a shape. I'm looking
up the index of a value in a range, and want to set the color of the
corresponding node in the shape. Any help is much appreciated.

Hello all,

I am trying to create a truss solving program. I would like the user to define the number of nodes in the truss and give coordinates for each node (i.e. node 1, node 2, etc). Then the user will create the truss members by defining their connecting nodes (i.e. member 1 is node 1 to node 2). I would like to create a plot of the truss to show the user what he or she is inputting. Is there a way to define the nodes as coordinates so that I could simply create another series of the nodes. I hope what I am describing is not too confusing. I have attached a small sample in which I want the four nodes to be connected according to the user-defined left and right nodes (i and j joints in spreadsheet).

Down to my last problem on this project,got some great help on this forum on solving problems. In the notebook you can compile a report on parent nodes with a child node
with no problems. If you try and compile a parent node without a child node you get error,(Object variable or With block variable not set). Need a message to come up if
someone tries to compile a parent node without a child. Maybe something like Nothing to Compile. I think this could be a handy tool for people that use Excel a lot,I use it as a add-in
to keep track of new and neat codes I find on the forum. Maybe someone has some more ideas for project to make it more useful.

Hello Guys...I need help here

I have a textfile and i'm using vba for excel to read the textline and paste in into excel..
I have a textfile that contain a thousand of data
the purpose of this project is to copy the textline from the starting text that i choose until it reach the end that i also choose..

here, i have attached a sample textfile that i need to copy to the excel...

fyi, A73, Z1 and A74 F+ is the node number..
For example i want to read A73 until node Z1 only, and my first node is A73, and the last node is Z1
I want it to read the all the textline until it reach the GRT1P1+U8+W8 line of Z1...
My problem is how do i do that...

FYI also, i have thousands of node number..This is just a sample..


This treeview controls is a real struggle for me.

Can anyone provide a code snippet to show how to check or uncheck all the child nodes of a parent node, when the parent node is checked/unchecked.

And if it's not too much trouble, how to check or uncheck the parent node when all the child nodes are either checked or unchecked.

I found this solution elsewhere, but I can't figure out how to modify this for Excel VBA.

Any help would be much appreciated.


Can I determine the position of a call out shape object, specifically the position of the starting node/the lowest node on the "triangle"? I need to figure out to what cell this call out is actually pointing to. This far, I have figured out how to get the width & height, and left & top position of the box. However, this is not accurate enough to predict that "starting node" position as the size of the shapes is different from one file to other files.

For example, as seen on the link below, is there any properties that can return the value of range (in this example, range "A6") or any pixel position somewhere close to there?

Thank you very much. Any answer will be very appreciated.

First off, here is a copy of my worksheet/chart.

I am trying to put together a simple bar graph in Excel 2003 that shows the mean of one dataset as a single bar and the mean of another dataset as another bar. Both of these will include y-error bars with the standard deviation for each dataset.

The problem I having is if I put each mean under a different series, Excel only puts one name for the (X) axis data label ("Node 1" instead of "Node 1" & "Node 3").

If I put each mean under the same series, the y-error bars for the standard deviation are the same for each bar. Each mean has its own standard deviation and needs to be independent.

I was wondering if someone can help me. This task may be simple for some, but I'm having some trouble.

I have an Excel document, and it isn't "too" complex, but it will not generate the XML correctly. Click the link below for what my Excel document looks like (not the original however).

As you can see, each "Description" has data underneath Titles 2-5. Each Description can have multiple of Titles 3-5 but only one of Title2.

I want this to be converted into XML. However, I want it to look like this.


	<title3 bla="TRUE">
	<title3 bla="FALSE">
	<title3 bla="FALSE">
	<title3 bla="TRUE">

	<title3 bla="TRUE">
	<title3 bla="TRUE">

The xml may seem slightly confusing. But please try to see how the Excel Example image turns into this XML. Each Description can have "multiple" of Title3, but it's still the same "node".

I'm not 100% on how to generate XML from Excel Documents, so I was hoping I could get some help. Sorry if this example is kind of confusing, if you would like more information, please ask a specific question and I'll do my best to respond as soon as possible.

Thank you,

Andrew Davis

Apologies if this has been asked before. I tried to search but didn't find anything that seemed to fit.

I have two columns:

Employee ID
Supervisor ID

I am trying to build a tree that goes from the various leaf nodes to a single root node.

At the top of the node is our CEO. from there I want to build the entire tree so that I can count how many people report into any given person. I don't want just the direct branches, I want a count of every branch at every level below the person of interest until I reach the final leaves.

Is there a way to do this in Excel? Access 2007 doesn't allow recursive SQL queries.

The rules I have come up with so far a
* each employee has a supervisor (except one, who is the root node)
* an employee who is not a supervisor is a leaf and thus the end of the recursion path

any assistance that can be provided would be most appreciated.

I know how to change an entire outline using Outline.ShowLevel. But how do you expand/contract a single node with vba? Does anyone know?