Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!

Subscribe for Free Excel tips & more!

Free Excel Forum

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 Default Number of Worksheets Created in a New Excel Workbook
Learn how to change the number of worksheets that are in new Excel workbooks. Excel workbooks always have 3 worksh ...
Loop through All Worksheets in Excel using VBA and Macros
Ill show you how to loop through all of the worksheets in a workbook in Excel using VBA and Macros. This only take ...
Loop Through an Array in Excel VBA Macros
I'll show you how to loop through an array in VBA and macros in Excel.  This is a fairly simple concept but it can ...

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.

I am trying to total 2 columns when the data in both columns match Row 6 and Row 16 are the same Row 5 and Row 19 are the same.

What Formula do I need in cell C2

******** ******************** ************************************************************************> Microsoft Excel - Book2 ___Running: xl2000 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout A1 =
A B C D 1 Work Sheet Row No     2 Hydraulics Node 39 1   3 Hydraulics Node 41 1   4 Water Node 7 1   5 Water Node 9 1   6 Water Node 15 1   7 Water Node 23 1   8 Pneumatics Node 7 1   9 Pneumatics Node 9 1   10 Pneumatics Node 10 1   11 Pneumatics Node 16 1   12 Pneumatics Node 17 1   13 Pneumatics Node 21 1   14 Water Node 20 1   15 LH 4 Button Door Panel 14 1   16 Water Node 15 2   17 LH 4 Button Door Panel 9 1   18 Pedal Link Node 12 1   19 Water Node 9 2   20 Pedal Link Node 18 1   Sheet1  
[HtmlMaker 2.41] To see the formula in the cells just click on the cells hyperlink or click the Name box

I've developed a workbook which acts as a sort of database, using one sheet as the entry point. Each time the user creates a new entry, VBA copies a template sheet (hidden) and names it according to the entry number on the first sheet; Node 1, Node 2, Node 3 etc.

This all works fine, and I've got referencing working and various other functions. I'm just trying to put a delete function in though and I'm a little confused as to how to go about it.

Basically, if I press the 'Delete Node' command button on the data entry sheet, it should lookup that Node number (e.g. 3), and delete that sheet - I've got this part working ok. But it should then shuffle all of the numbers after 3 down by one, so it would need to first identify how many sheets there are named Node x with numbers above 3, and rename them all as Node x-1, whilst also changing the number in cell (4, 3) on each sheet to x-1 as well.

How could this be done?

I'm currently using this sort of formula to look up each sheet:


Sheets("Node " & Sheets("Data_Entry").Range("Nodenumber")).Select

So the 'Delete' script looks something like this at the moment:

Sub DeleteNode()
If MsgBox("Delete This Node?", vbYesNo, "Discard Changes") = vbYes Then
GoTo Delete
MsgBox ("Deletion Cancelled")
Exit Sub
End If
Application.ScreenUpdating = False
Call Unhide
If MsgBox("Are you sure?", vbYesNo, "Discard Changes") = vbYes Then
Sheets("Node " & Sheets("Data_Entry").Range("Nodenumber")).Select
Application.DisplayAlerts = False
Application.DisplayAlerts = True
Sheets("Calcs").Range("CurrentNodes").Value = Sheets("Calcs").Range("CurrentNodes").Value - 1
Sheets("Data_Entry").Range("Nodenumber").Value = Sheets("Data_Entry").Range("Nodenumber").Value - 1
Sheets("Data_Entry").Range("Date").Value = ""
Sheets("Data_Entry").Range("Node_Description").Value = ""
Sheets("Data_Entry").Range("Node_Design_Intention").Value = ""
Sheets("Data_Entry").Range("Drawings_Used").Value = ""
On Error GoTo Skip
Sheets("Data_Entry").Range("SelectNode").Value = Sheets("Data_Entry").Range("Nodenumber").Value
Call SelectNode
Call hide
Application.ScreenUpdating = True
MsgBox ("Node Deleted")
Exit Sub
MsgBox ("Deletion Cancelled")
End If
End Sub

Any help would be really appreciated!

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.

All data is copied in the first column i.e. A
Below is a sample of data that I need to work with. it is comprised of text and other weird format. The numbers that I am interested in have a space 0. example - "89.45 0"
In these figures, I need to find the values that come below a cell having value - failed.
below the cell that has failed, will always be a cell having value - node this is.
now I need to count the number of rows of decimals that are coming below the failed cell.
for example, using the below data, the data will be pasted starting from cell A1. Below the first "failed" cell value, comes "the node this is" cell, below this cell are the decimal numbers that have failed. unfortunately there is no clear end to the failed data except that the cell "node this is" is repeated. this will mark the end of the data that needs to be copied to a different location. The "failed" value can be repeated in the column, and I need the cells having the decimal formats.

To make things simple and sum up the requirement. I need the values that come under "Failed" and that is starting after "node this is" and ending before the next " node this is"

89.45 0
and there is
node this is
76.98 0
32.78 0
12.89 0
here is
node this is
98.56 0
node this is
43.89 0
56.65 0
node this is


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..


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...Sample.txt

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..


Dear All,

I need to form a matrix in Excel based on user inputs satisfying below Conditions: Pls help me in writing a macro or necessary worksheet functions to develop the same.. Mail to .. Thnx

S.No. -- leftNode# --- RightNode#
1 ------- 1 -------- 5
2------- 2------- 4
3------- 3 ------- 4
4------- 4------- 5
5------- 5------- 6
6------- 5------- 6
7------- 6------- 7
8------- 4------- 8
1. Check the left node of element 1, if that node is appearing for first time (in user input area), then populate 1 in corresponding Q1 location
2. Similarly check for other elements and populate 1, if above condition is satisfied
3. If left node of any element is connected to any previous or following element, populate 1 on corresponding element locations, if that node no. appears on right (in user input table), and -1, if it comes on left
4. If any of the left node no. is repeated more than once for elements, then populate 1 on corresponding location of first element
5. In the remaining locations, it should populate zero

Resultant Matrix should be:

8x8 matrix

1 0 0 0 0 0 0 0
0 1 0 0 0 0 0 0
0 0 1 0 0 0 0 0
0 1 1 0 0 0 0 -1
0 0 0 0 1 0 0 0
1 0 0 1 -1 0 0 0
0 0 0 0 1 1 0 0
0 1 1 -1 0 0 0 0

Thanks in advance,

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.