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

Determine Minimum Indent Level Of Selected Cells

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

I need to determine if any of the cells selected by the user have an indent level equal to 1 and if so then have them confirm the action. the below works well if the user only selects one cell but if the select 2 or more and any of the selected cells has an indent level greater than 1 it doesn't show the confirmation.


Sub Indent_Minus()
    If Selection.IndentLevel = 1 Then ' ???If the user selects more than one row and any row has an indent level greater than 1 then it doesn't funciton
        Dim iDeleteConfirm As Integer
        iDeleteConfirm = MsgBox("Decreasing the indent will crate a new project, are you sure?", _
            vbYesNo + vbQuestion, "Confirm Indent")
            If iDeleteConfirm = vbYes Then
                GoTo Confirm
            End If
                ActiveSheet.Unprotect Password:=""
                Selection.InsertIndent -1
                Run "EndRow"
                Run "WBSNumbering"
                Exit Sub
        End If
    On Error GoTo ErrHandler
    ' error handling code
    Run "ProtectMe"
    Resume Next
    End Sub

View Answers     

Similar Excel Tutorials

How to Arrange Data within Cells in Excel
In this tutorial I am going to look at cell alignment / arrangement. These features allow you to change how data lo ...
Generate Random Numbers within a Range in Excel
How to generate random whole numbers (integers) that are between two numbers.  This allows you to set a minimum and ...
Excel Data Validation - Limit What a User Can Enter into a Cell
Data Validation is a tool in Excel that you can use to limit what a user can enter into a cell.  It is a great too ...
Shade Every Other Row in Excel Quickly
How to shade every other row in Excel quickly without using the Table feature. This method is for when you simply w ...

Helpful Excel Macros

Excel Macro to Save a Specific Worksheet as a New File
- This Excel Macro allows you to save a specific worksheet within the Excel Workbook to its own new file. You will be a
Macro to add a New Line to Message Box Pop-up Windows in Excel
- This is a very simple Message Box, pop-up window, macro for Excel that illustrates how to put new lines, the same thi
Save the Current Worksheet as a New Excel Workbook File
- This Excel Macro will save the currently visible/active worksheet (the one that you see when you run the macro) to a
Complete Guide to Printing in Excel Macros - PrintOut Method in Excel
- This free Excel macro illustrates all of the possible parameters and arguments that you can include in the PrintOut Meth
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

Similar Topics

I have a userform with 2 listbox. Selections in listbox1 are added to the string SelectedArea and listbox2 to SelectedRegion.

Below, I have a code that will make sure the user selects at least one option from one listbox. If they do not, the appropriate message box pops up telling the user what they didn't fill out and the sends the user back to the userform.

If they've made at least one selection from each listbox, I have a message that pops up asking them to confirm their selections. This message box has both a yes and no button. Yes should exit the message and continue with the rest of the code, no sends them back to the userform like the rest of the error messages

My problem is that when the user hits the yes button, they are currently being sent back to the userform. How do I fix this?



If SelectedArea = vbNullString Then[INDENT]If SelectedRegion = vbNullString Then[/INDENT][INDENT][INDENT]Check = MsgBox("You must select at least one City and Region" & _[/INDENT][/INDENT][INDENT][INDENT][INDENT]vbNewLine & vbNewLine & vbNewLine & _[/INDENT][/INDENT][/INDENT][INDENT][INDENT][INDENT]"You can manually input the data on the 'Input' sheet", _[/INDENT][/INDENT][/INDENT][INDENT][INDENT][INDENT]vbInformation, "No Area and Region Selected!")[/INDENT][/INDENT][/INDENT][INDENT][INDENT]Exit Sub[/INDENT][/INDENT][INDENT]Else[/INDENT][INDENT][INDENT]Check = MsgBox("You must select at least one City" & _[/INDENT][/INDENT][INDENT][INDENT][INDENT]vbNewLine & vbNewLine & vbNewLine & vbNewLine & _[/INDENT][/INDENT][/INDENT][INDENT][INDENT][INDENT]"You can manually input the data in the 'Input' sheet", _[/INDENT][/INDENT][/INDENT][INDENT][INDENT][INDENT]vbInformation, "No Trade Area Selected!")[/INDENT][/INDENT][/INDENT][INDENT][INDENT]Exit Sub[/INDENT][/INDENT][INDENT]End If[/INDENT]Else[INDENT]If SelectedRegion = vbNullString Then[/INDENT][INDENT][INDENT]Check = MsgBox("You must select a Region" & _[/INDENT][/INDENT][INDENT][INDENT][INDENT]vbNewLine & vbNewLine & vbNewLine & vbNewLine & _[/INDENT][/INDENT][/INDENT][INDENT][INDENT][INDENT]"You can manually input the data in the 'Input' sheet", _[/INDENT][/INDENT][/INDENT][INDENT][INDENT][INDENT]vbInformation, "No Region Selected!")[/INDENT][/INDENT][/INDENT][INDENT][INDENT]Exit Sub[/INDENT][/INDENT][INDENT]Else[/INDENT][INDENT][INDENT]Check = MsgBox(vbNewLine & SelectedArea & vbNewLine & _[/INDENT][/INDENT][INDENT][INDENT][INDENT]"In " & SelectedRegion & vbNewLine & vbNewLine & _[/INDENT][/INDENT][/INDENT][INDENT][INDENT][INDENT]"Are you happy with your selection?", _[/INDENT][/INDENT][/INDENT][INDENT][INDENT][INDENT]vbYesNo + vbInformation, "You Selected the Following:")[/INDENT][/INDENT][/INDENT][INDENT][INDENT]SelectedArea = ""[/INDENT][/INDENT][INDENT][INDENT]SelectedRegion = ""[/INDENT][/INDENT][INDENT]End If[/INDENT]End If 
Exit Sub 

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

This is a somewhat complicated problem but there are some amazing people out there who have helped before. Thanks in advance!

I have a userform with with a multiselect listbox. When a value (or values) is selected it is listed on sheet("Model Calculations"). Data is also collected about the selection from a database in a 2nd sheet(wsrCty) and listed in the columns next to the selection on "Model Calculations".

Currently, if the selected value is not found in wsrCty, "N/A" is placed in the adjacent columns and the value is added to a string so that the user can be alerted with a message.

Occasionally a value in the database related to the selection is "#" instead of a number. When this happens I need the user to be alerted by a message similar to the one I use if the selection is not found in the database. The message would read " 'Column B' data not available for 'selection 1' " (if # was listed in column B for selection 1) for each time # occurs

Here's a portion of my code to list all selections and their associated data, plus the error message if the selection is not in the database

Dim Res As Variant, NoRetail As String[INDENT]With lbCity[/INDENT][INDENT][INDENT]For lbc = 0 To .ListCount - 1[/INDENT][/INDENT][INDENT][INDENT]If .Selected(lbc) Then[/INDENT][/INDENT][INDENT][INDENT][INDENT]On Error Resume Next[/INDENT][/INDENT][/INDENT][INDENT][INDENT][INDENT][INDENT]Err.Clear[/INDENT][/INDENT][/INDENT][/INDENT][INDENT][INDENT][INDENT][INDENT]Res = wsrcty.Range("B:Q").Find(.List(lbc, 1)).Offset(5, 0)[/INDENT][/INDENT][/INDENT][/INDENT][INDENT][INDENT]If Err.Number = 0 Then[/INDENT][/INDENT][INDENT][INDENT][INDENT]Worksheets("Model Calculations").Cells(Rows.Count, "A").End(xlUp)(2) = .List(lbc)[/INDENT][/INDENT][/INDENT][INDENT][INDENT][INDENT]Worksheets("Model Calculations").Cells(Rows.Count, "B").End(xlUp)(2) = _[/INDENT][/INDENT][/INDENT][INDENT][INDENT][INDENT][INDENT]wsrcty.Range("B:Q").Find(.List(lbc, 1)).Offset(5, 0)[/INDENT][/INDENT][/INDENT][/INDENT][INDENT][INDENT][INDENT]Worksheets("Model Calculations").Cells(Rows.Count, "C").End(xlUp)(2) = _[/INDENT][/INDENT][/INDENT][INDENT][INDENT][INDENT][INDENT]wsrcty.Range("B:Q").Find(.List(lbc, 1)).Offset(6, 0)[/INDENT][/INDENT][/INDENT][/INDENT][INDENT][INDENT][INDENT]Worksheets("Model Calculations").Cells(Rows.Count, "D").End(xlUp)(2) = _[/INDENT][/INDENT][/INDENT][INDENT][INDENT][INDENT][INDENT]wsrcty.Range("B:Q").Find(.List(lbc, 1)).Offset(7, 0)[/INDENT][/INDENT][/INDENT][/INDENT][INDENT][INDENT]Else[/INDENT][/INDENT][INDENT][INDENT][INDENT]NoRetail = NoRetail & .List(lbc) & vbNewLine[/INDENT][/INDENT][/INDENT][INDENT][INDENT]Worksheets("Model Calculations").Cells(Rows.Count, "A").End(xlUp)(2) = .List(lbc)[/INDENT][/INDENT][INDENT][INDENT][INDENT]Worksheets("Model Calculations").Cells(Rows.Count, "B").End(xlUp)(2) = "N/A"[/INDENT][/INDENT][/INDENT][INDENT][INDENT][INDENT]Worksheets("Model Calculations").Cells(Rows.Count, "C").End(xlUp)(2) = "N/A"[/INDENT][/INDENT][/INDENT][INDENT][INDENT][INDENT]Worksheets("Model Calculations").Cells(Rows.Count, "D").End(xlUp)(2) = "N/A"[/INDENT][/INDENT][/INDENT][INDENT][INDENT]End If[/INDENT][/INDENT][INDENT]End If[/INDENT][INDENT]Next lbc[/INDENT]End With 
Dim checkb As String 
If NoRetail = vbNullString Then[INDENT]Unload Me[/INDENT][INDENT]Exit Sub[/INDENT]Else[INDENT]checkb = MsgBox("The model does not contain following areas:" _[/INDENT][INDENT][INDENT]& vbNewLine & vbNewLine & NoRetail & vbNewLine & _[/INDENT][/INDENT][INDENT][INDENT]"You can manually enter the data on the 'Input' sheet", vbInformation, "Warning!")[/INDENT][/INDENT][INDENT]NoRetail = ""[/INDENT]End If 

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


I'm having trouble getting my userform to properly use an offset formula. I am using (attempting to use) a find function to find a listbox value as the anchor cell for the offset function.

Appreciate all the help!


With lbT[INDENT]For i = 0 To .ListCount - 1[/INDENT][INDENT][INDENT]If .Selected(i) Then[/INDENT][/INDENT][INDENT][INDENT][INDENT][INDENT]Worksheets("AutoRun").Cells(Rows.Count, "D").End(xlUp)(2) = .List(i)[/INDENT][/INDENT][/INDENT][/INDENT][INDENT][INDENT][INDENT][INDENT]Worksheets("AutoRun").Cells(Rows.Count, "E").End(xlUp)(2) = _[/INDENT][/INDENT][/INDENT][/INDENT][INDENT][INDENT][INDENT][INDENT][INDENT]Worksheets("Test").Range("B:E").Application.WorksheetFunction. _[/INDENT][/INDENT][/INDENT][/INDENT][/INDENT][INDENT][INDENT][INDENT][INDENT][INDENT]Find(.List(i)).Offset(1, 1)[/INDENT][/INDENT][/INDENT][/INDENT][/INDENT][INDENT][INDENT]End If[/INDENT][/INDENT][INDENT]Next i[/INDENT]End With 

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

I have a multi-select listbox. For simplicity lets say that its populated with A thru Z plus 'Group'. Group appears first in the list and is supposed to be the combination of A, C, G, K, and T from the list. Basically Group is there because this combination of selections is very common so its easier for the user to select Group than all 5 selections each time.

I have a code (below) that will identify a selection, run a series of formulas based on that selection then move to the next selection. The code also can identify if 'Group' is selected.

Does anyone know what codes to use so that when Group is selected, the model will run as if A, C, G, K, and T where selected in addition to the rest of the listbox selections?

Hope that's not too complicated. Thanks!


With lbCounty[INDENT]For lbr = 0 To .ListCount - 1[/INDENT][INDENT]NextColumn = Worksheets("Example").Cells(3, Columns.Count).End(xlToLeft).Column[/INDENT][INDENT][INDENT]If .Selected(lbr) Then[/INDENT][/INDENT][INDENT][INDENT][INDENT] 'if Group selected[/INDENT][/INDENT][/INDENT][INDENT][INDENT][INDENT]If .List(lbr) = "Group" Then[/INDENT][/INDENT][/INDENT][INDENT]'enter code here that will run formulas for A, C, G, K, T[/INDENT][INDENT][INDENT][INDENT]Else[/INDENT][/INDENT][/INDENT][INDENT]'normal code for all other selections[/INDENT]

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


I have built a multiselect listbox that places the selected items in column D of my input sheet. The listbox is populated with values from sheet 2. I've been trying to come up with a code that will use each listbox selection to search sheet 3 (the listbox names are listed in column A) and pull data from a different column and place it on the input sheet in column E. So if I select "AA, BB, and CC" in the listbox, the code will use vlookup or offset or whatever works to pull values related to those 3 selections from sheet 3, column F and place those values on the input sheet.

Hopefully that's not too confusing. Here is my code which works to place the listbox values onto the input sheet but fails with the vlookup attempt

Any help is much appreciated

With listbox1[INDENT]For i = 0 To .ListCount - 1[/INDENT][INDENT][INDENT]If .Selected(i) Then[/INDENT][/INDENT][INDENT][INDENT][INDENT]Cells(Rows.Count, "D").End(xlUp)(2) = .List(i)[/INDENT][/INDENT][/INDENT][INDENT][INDENT][INDENT]Cells(Rows.Count, "E").End(xlUp)(2) = _[/INDENT][/INDENT][/INDENT][INDENT][INDENT][INDENT][INDENT]Application.WorksheetFunction.VLookup(.List(i), _[/INDENT][/INDENT][/INDENT][/INDENT][INDENT][INDENT][INDENT][INDENT]ActiveWorkbook.Sheets("sheet3").Range("A:F"), 6, False)[/INDENT][/INDENT][/INDENT][/INDENT][INDENT][INDENT]End If[/INDENT][/INDENT][INDENT]Next i[/INDENT]End With 

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

I currently have a 2 column listbox (only the first column is shown) which I am having trouble expanding to a 6 column listbox. My code populating the listbox is below. Can anyone advise on this? With 2 columns if I use the code ".list(i,1)" it pulls up the second column, but ".list(i,5)" does not work

Secondly, there are repeating values in the 6th column that I want to pull up - the first column lists cities, the 6th lists what county they are in. Is there a way to remove duplicate counties from appearing in the results? So if I select 4 cities from the listbox (3 of which are in one county and 1 in another), the userform will only post the 2 counties (the first county is listed 1 time not 3). I would like this information listed on sheet1 column B.


Code for populating list:

Private Sub UserForm_Initialize() 
    Dim rng As Range[INDENT]With lbC[/INDENT][INDENT][INDENT].RowSource = ""[/INDENT][/INDENT][INDENT][INDENT].ColumnCount = 6[/INDENT][/INDENT][INDENT][INDENT].ColumnWidths = .Width & ";0"[/INDENT][/INDENT][INDENT][INDENT]For Each rng In Worksheets("InputData").Range("G2:G" & _[/INDENT][/INDENT][INDENT][INDENT][INDENT]Worksheets("InputData").Range("G65536").End(xlUp).Row)[/INDENT][/INDENT][/INDENT][INDENT][INDENT][INDENT].AddItem rng.Value[/INDENT][/INDENT][/INDENT][INDENT][INDENT][INDENT].List(.ListCount - 1, 1) = rng.Offset(, 1)[/INDENT][/INDENT][/INDENT][INDENT][INDENT]Next rng[/INDENT][/INDENT][INDENT]End With[/INDENT]End Sub 

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

Running userform:

Private Sub cbRun_Click() 
    Dim i As Long[INDENT]With lbC[/INDENT][INDENT][INDENT]For i = 0 To .ListCount - 1[/INDENT][/INDENT][INDENT][INDENT][INDENT]If .Selected(i) Then[/INDENT][/INDENT][/INDENT][INDENT][INDENT][INDENT][INDENT]Worksheets("sheet1").Cells(Rows.Count, "A").End(xlUp)(2) = .List(i)[/INDENT][/INDENT][/INDENT][/INDENT][INDENT][INDENT][INDENT]End If[/INDENT][/INDENT][/INDENT][INDENT][INDENT]Next i[/INDENT][/INDENT][INDENT]End With[/INDENT]End Sub 

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

I have a multiselect listbox that outputs the selection in the first open row in column A.

I need to change this so that the listbox selection will be placed in the first open column in row 1. I'm sure its a simple switch but what I've tried so far isn't outputting properly or causes a runtime error

Here's my original code. Thanks!


With lbCity[INDENT]For lbc = 0 To .ListCount - 1[/INDENT][INDENT]If .Selected(lbc) Then[/INDENT][INDENT][INDENT]Worksheets("Model Calculations").Cells(Rows.Count, "A").End(xlUp)(2) = .List(lbc)[/INDENT][/INDENT][INDENT]End If[/INDENT][INDENT]Next lbc[/INDENT]End With 

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


I've written a macro that will create a worksheet if it is missing from the workbook. I would like to add a line that will then order all of the worksheets in a customized order by tab name (not alphabetical or by CodeName) so that the missing worksheet is in the right place within the workbook.

Here's part of my code to create the missing worksheets:

Sub SetWorksheets() 
    [INDENT]Dim Demo As String[/INDENT][INDENT]Demo = "Demo"[/INDENT][INDENT]On Error Resume Next[/INDENT][INDENT][INDENT]Set AddSheetIfMissingA = ThisWorkbook.Worksheets(Demo)[/INDENT][/INDENT][INDENT]If AddSheetIfMissingA Is Nothing Then[/INDENT][INDENT][INDENT]Set AddSheetIfMissingA = ThisWorkbook.Worksheets.Add[/INDENT][/INDENT][INDENT][INDENT][INDENT]AddSheetIfMissingA.Name = Demo[/INDENT][/INDENT][/INDENT][INDENT][INDENT][INDENT]Worksheets(Demo).Tab.ColorIndex = 35[/INDENT][/INDENT][/INDENT][INDENT]End If[/INDENT][INDENT][/INDENT][INDENT][/INDENT][INDENT]Dim Comp As String[/INDENT][INDENT]Comp = "Comp"[/INDENT][INDENT]On Error Resume Next[/INDENT][INDENT][INDENT]Set AddSheetIfMissingB = ThisWorkbook.Worksheets(Comp)[/INDENT][/INDENT][INDENT]If AddSheetIfMissingB Is Nothing Then[/INDENT][INDENT][INDENT]Set AddSheetIfMissingB = ThisWorkbook.Worksheets.Add[/INDENT][/INDENT][INDENT][INDENT][INDENT]AddSheetIfMissingB.Name = Comp[/INDENT][/INDENT][/INDENT][INDENT][INDENT][INDENT]Worksheets(Comp).Tab.ColorIndex = 35[/INDENT][/INDENT][/INDENT][INDENT]End If[/INDENT]End Sub 

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

Also, not that it matters, but is there a way to set the CodeName so that a worksheet is always created as sheet1 or sheet2, etc?

Thanks for all the help


I hope someone can help me with the vb code. I'm new to vb so I'm sure I'm missing the obvious.....

I am inmporting from Ms Project 2003 to Excel 2003. The text in B is what needs to be indent based on the number in J. Excel doesn't indent.level any number above 8, I have 10 levels. I'm trying to write a work around. If the level number in J is less than 6, indent two spaces, if greater than 6 indent 1 space.

Here's the code (someone else wrote it) and I'm trying to make it work for my application.

Its stops on: If "J:J" < 6 Then



Dim projectname As String
projectname = B1
Dim counter As Integer
counter = 0
With Selection
.Offset(1, 0).Range("A1").Select
.WrapText = True
If "J:J" < 6 Then
.IndentLevel = (((ActiveCell.Offset(0, 8)) - 1) * 2)
.IndentLevel = (ActiveCell.Offset(0, 8))
End If
End With

I have a userform with several textbox (tb1, tb2, tb3). When the form opens, the textbox are populated from values in a worksheet:


Private Sub UserForm_Initialize()[INDENT]tb1.Text = Format(Worksheets("Input").Cells(1, 1), "$#,##0")[/INDENT][INDENT]tb2.Text = Format(Worksheets("Input").Cells(2, 1), "0%")[/INDENT]End Sub 

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

The code works, but since the textbox are being populated with numbers I'm assuming the code needs to be changed to remove the "text" part. I tried entering "number" instead of text but got an error message. (Note- "N/A" is a possible option for the values)

Is there a code for the textbox to keep its formatting when a user enters in a new number? (If they enter 12345 into tb1, the textbox goes to $12,345 when the user clicks outside of the textbox)

For tb3, I need a code that will add tb1 and tb2. If the user changes either tb1 or tb2, tb3 will automatically update


Hi All,

I was wondering if it was possible to do a SUMIF function depending on the indent level, e.g. sum all of the values which have an indent of 2 in the row headings.

Any words of wisdom, greatly appreciated.



I need to be able to look up the highest value in one cell and the lowest number in another cell from a column of information. For example:

For Part A, I would like one cell to populate 100 for the lowest value and another cell to populate with 110 for the highest value.

I assume I can use the MIN and MAX formula, and tried the formula below, but it does not work when there are multiple entries of the same Part #. Any suggestions?

The formula I tried to use was:


I tried researching this but didn't come up with anything that worked quite right. I need to create a vba code that puts a border around the outside of the selected cells (all of the cell borders on the inside are not included)

I tried the following but keep getting runtime error '424'

With Worksheets("Sheet1").Range("B8:I10").BorderAround[INDENT].LineStyle = xlContinuous[/INDENT][INDENT].Weight = xlThick[/INDENT]End With 

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

The selection ranges from a few columns in one row to many rows and columns if this makes a difference


I need some help building a script that will create individual workbooks based on data from one sheet. The data need to be copied to the new file based on the store number and the file name will be a combination of company name followed by location ID and store ID. With the sample below, the script would create three files (Abc company10171.xls,111 company33233.xls,Smith Company635162.xls). There will be over 50 companies and the location ID and store ID will always be unique the same for each Company

Location ID[INDENT]Store ID[INDENT]Company Name[INDENT]Data
10[INDENT]171[INDENT]abc company[INDENT]Details
10[INDENT]171[INDENT]abc company[INDENT]Details
33[INDENT]223[INDENT]111 company[INDENT]Detials
33[INDENT]223[INDENT]111 company[INDENT]Detials
635[INDENT]162[INDENT]Smith company[INDENT]Detials

I have save an MS Project plan as an Excel workbook. The problem is that the outlining of the project plan didn't come across - well, the task names aren't indented, but the outline level did come across. So, I see 1, 2, 3, 4 in outline level, so I assume I can do some kind of formula in Excel that says if outline level=2 then indent once, if outline level=3 then indent twice, etc. Any ideas?

In the code below if the user selects a row in column A that is less than 3 (3rd row) they will be prompted to make a selection from row 3 or greater. This works until the user selects a row from 10 or greater.
I keep getting the result as if the user selected a row less than the 3rd row?

The code "Split(ActiveCell.Address, "$")(2)" does report the correct value of the row selected (verified via msgbox).


Option Explicit

Sub CheckForDIP()
    Dim sFilename As String

    If Split(ActiveCell.Address, "$")(1) <> "A" Then
        MsgBox "You must select a cell from column A only!"""
        Exit Sub
    End If
    If ActiveCell.Value = "" Then
        MsgBox "You selected an empty cell!"
    End If
    If Split(ActiveCell.Address, "$")(2) < "3" Then
        MsgBox "You must select a cell from Row 3 or greater!"""
        Exit Sub
    End If
    If Split(ActiveCell.Address, "$")(1) = "A" And _
        ActiveCell.Value <> "" Then
            sFilename = ActiveCell.Value
            On Error GoTo 0
            If Len(Dir("M:\Qadocs\DIPS\" & sFilename & ".xls")) > 0 Or _
               Len(Dir("M:\Qadocs\DIPS\" & sFilename & ".xlsx")) > 0 Then
                MsgBox "A current DIP exists as file name: " & sFilename
                MsgBox "DIP not found!"
            End If
        'End If
    End If
End Sub

Good Day,

Is there a way to figure out the indentation without VBA Code? For various reasons, a macro is the last resort for this operation.
I'm trying to create some sort of numbering for the adjacent cells according to their indent level (IL) Something like this:
IL = 0 (i.e. no indent) => 1st Level Numbering (1, 2, 3, etc)
IL = 1 => 2nd Level Numbering (1.1, 2.1, etc)
IL = 2 => 3rd Level Numbering (1.1.1, 2.1.2, etc)

And so on.
A related question:
What's a better way of generating the numbering level?
Thanks for the help in advance.

Okay, so I'm somewhere around an intermediate level in terms of my skill with Excel and VBA. Right now, I'm having trouble with something.

I have a form which contains a list box (called listBoxDisplay), and the information that populates the list box with the rowSource property will vary depending on user input.

I'm trying to adapt code that I wrote for a small database where all the data is contained in one excel file. Now, the data to be retrieved by the rowSource is in an external file.

When all the data was contained in one workbook, the code was as follows:

[INDENT]dim slavesheet As object[INDENT]slaveSheet Is the global Object I created that Is used To determine the worksheet To retrieve data from 
With listBoxDisplay[INDENT].RowSource = Range(Cells(1, 1), Cells(lastRowInSheet, lastColumnInSheet)).Address 
    [/INDENT]End With 

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

How can I adapt this to reference ranges in an external excel file?

Hello All,

I am hoping to get some help here as I have tried to do a lot of search but none matched my requirements. Just in parts and pieces

BTW I am totally new to VBA

I am trying to get a list of folders and subfolders along with the files in each with the hyperlink.

The code below that I got from another forum pulls the directory structure and indents the subfolders in ColA and gives the path of the subfolder in ColB. What I would like instead is to have the list of folder names in colA and the list of associated files for each subfolder in ColumnB hyperlinked

Please see the attachments
Sheet 1 is the code below running
Sheet 2 is what I would like


Sub ListThem() 
    Dim startRange As Range 
    Set startRange = Sheet1.Range("A1") 
     'Parent Directory - Change this to whichever directory you want to use
    ListFoldersAndInfo    "C:\Users\MyName\Documents\Application Data", startRange, 0 
End Sub 
Sub ListFoldersAndInfo(foldername As String, Destination As Range, Level As Long)[INDENT]Dim FSO As Object 
    Dim Folder As Object 
    Dim R As Long 
    Dim SubFolder As Object 
    Dim Wks As Worksheet 
    [/INDENT][INDENT][INDENT]Set FSO = CreateObject("Scripting.FileSystemObject")[INDENT]Set Folder = FSO.GetFolder(foldername) 
    Destination = Folder.Name 
    Destination.IndentLevel = Level 
    Destination.Offset(0, 1) = Folder.Path 
    Destination.Offset(0, 2) = Folder.Size 
    Set Destination = Destination.Offset(1, 0) 
    For Each SubFolder In Folder.SubFolders[INDENT]ListFoldersAndInfo Folder.Path & "\" &  subFolder.Name, Destination, Level + 1 
        [/INDENT]Next SubFolder 
        [/INDENT]Set FSO = Nothing 
        [/INDENT][/INDENT]End Sub 

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

Y'alls help would be greatly appreciated


Hi, I got a code from that enables the user to select a row to delete. However, I need to do more than that. I need to be able to copy all the rows until row 37 and move it one row up after the selected row is deleted. Below is the code


Private Sub CommandButton1_Click()
Dim MySelection As Range
    Dim msg As String, Title As String
    Dim Config As Integer, Ans As Integer
    msg = "The selected project will be permanently deleted from the  database! Continue ?"
    On Error Resume Next
    Title = "Confirm Row Deletion"
    Config = vbYesNo + vbExclamation
    Set MySelection = Application.InputBox(prompt:="Click on the Name of the Project you wish to delete", Title:="Project Deletion", Type:=8)
     Dim ab As Integer
    MySelection.Address = "$A$" & ab 
    MyErr = Err
    On Error GoTo 0
    If MyErr <> 0 Then
         MsgBox "You pressed Cancel, this procedure will now terminate."
        Exit Sub
    End If
    Ans = MsgBox(msg, Config, Title)
    If Ans = vbYes Then
        Cells(ab+1, 2).Select
        Selection.Pastespecial Paste:= xlPasteAll
        MsgBox ("Your selection was successfully deleted from the Database!"), vbInformation

    End If

The part of the code that is red is the part causing the problem, "Assignment to constant not permitted". Is there another way to get the row number 'ab'? Thanks in advance!

I've searched the forum and found several examples that are close to what I need to do but my deductive reasoning juices aren't flowing today and I need help.

I need a macro to indent a cell in column A by "2" if the corresponding cell in column B = "yes"

This "step" will be placed as the last step in a much larger macro which is why I'm looking for the code and not trying to use a formula.

Any help would be greatly appreciated.

I am having trouble getting a formula to work.
The formula looks at 3 different cells to determine at what "level" an individual should be ranked. (Below)

=IF(F4<2,"Level I",IF(AND(B3>1.99,F7>29.99),"Level II",IF(AND(B3>3.99,F7>39.99,F4>9.99),"Level III",IF(AND(B3>7.99,F7>59.99,F4>14.99),"Level IV",IF(AND(B3>17.99,F7>79.99,F4>19.99),"Level V","")))))

Not sure what I did wrong but when I test drive it I can't get anything above Level II even if I max out all the values.

Hi. I need to protect a sheet, allow users to enter text in unlocked cells AND use the 'Increase indent' and 'Decrease indent' toolbar.

I am using Excel 2003 and none of the protection checkboxes will allow the 'Increase indent' and 'Decrease indent' toolbars to work when proection is on. I find this odd because you can increase/decrease the indent when the protection is on by using Format > Cells > Alignment. Also, I can get all the other formatting toolbars to work except increse/decrease indent.

This should be a simple one for you guys/gals to help me with!

OK, second string question this evening.

This procedure looks at the strings in column A (which have a series of dots
in them) and "should" add a space for every dot found to the front of the
string in the respective row in column B.

The problem is that I'm getting a blank value ("") for variable convStr even
though there is a string in the right place in column B.

Any clues?

Thanks again


Sub IndentListWithSpaces()

'Run down list and indent cell values to right, dependent on number of dots
in string
Dim rgListItem As Range
Dim convStr As String
Dim nDots As Integer
Dim r As Integer
Dim c As Integer
Dim totalStr As String

'Check user selects cell at top of list
Answer = MsgBox(Prompt:="Is cell at top of list selected?", Buttons:=vbYesNo
+ vbQuestion)
If Answer = vbNo Then Exit Sub

r = ActiveCell.Row
c = ActiveCell.Column

Set rgListItem = Cells(r, c)
If IsEmpty(Cells(r, c)) Then Exit Do
totalStr = rgListItem.Value
nDots = 0
For x = 1 To Len(totalStr)
If Mid(totalStr, x, 1) = "." Then
nDots = nDots + 1
End If
Next x

For y = 1 To nDots
convStr = rgListItem.Offset(r, (c + 1)).Value
rgListItem.Offset(r, (c + 1)).Value = " " & convStr
Next y

r = r + 1


Cells(r, c).Select

MsgBox "Finished"

End Sub

Sometimes when I'm working with text cells I get this tiny indent on the left hand side of a cell about the size of one hit of the spacebar button.
Excel doesn't recognise this as an indent and I can't get rid of it. It's, pardon my french, really f*cking annoying and how do I stop it from happening??!?!!!!!!