Email:      Pass:    Pass?
Advertisements


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.



Code:

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
            Else
Confirm:
                ActiveSheet.Unprotect Password:=""
                Selection.InsertIndent -1
                Run "EndRow"
                Run "WBSNumbering"
                Exit Sub
        End If
    On Error GoTo ErrHandler
ErrHandler:
    ' error handling code
    Run "ProtectMe"
    Resume Next
    
    End Sub





Similar Excel Video Tutorials

Helpful Excel Macros

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
Hide Formulas in a Worksheet and Prevent Deletion
- This macro will hide all formulas within a workbook and not allow them to be deleted. The page will not be protected li
Protect and Unprotect All Worksheets at Once
- This will allow you to protect or unprotct all worksheets in a workbook at once. It doesn't allow you to input a passwor
Automatically Highlight the Active or Selected Cell
- This macro will highlight the active cell in any excel spreadsheet with a color selected in the code. This means that a
Highlight the Row of the Selected Cell
- This macro will highlight the row and column of the active cell. This will fill the row of the active cell with a solid

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?

Thanks!

VB:

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
VB:

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




Hi

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!

VB:

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




Hi

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
VB:

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


VB:

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

Thanks!

Code for populating list:
VB:

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:
VB:

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




Hi all:
Looking to format a BOM down-load (into Excel), so that the BOM levels are indented according to the level number.
Levels are indicated in Column C. Columns to indent would be C thru F.
Number of rows is undetermined.

Setting up the code, I have the following - although so far mostly instructions without code.
I hope the requirements are expressed clearly enough.

Code:

Sub BomIndentCols()

    Dim LastRow As Integer

'   Set Columns to be Indented to Default Left Align
        Range("C:F").HorizontalAlignment = xlLeft

'   Determine Last Row for THIS Spread-Sheet
        LastRow = Cells(Rows.Count, "E").End(xlUp).Row

'   Go to C, Last Row
        Range("C" & LastRow).Select

    '   If C = Blank, Do Nothing
    '   If C = 0, Do Nothing
    '   If C = 1, Indent 2 spaces
    '   If C = 2, Indent 4 Spaces
    '   If C = 3, Indent 6 Spaces
    '   If C = 4, Indent 8 Spaces
    '   If C = 5, Indent 10 Spaces
    '   If C = 6, Indent 12 Spaces

    '   Indent Columns C, D, E & F,  In current Row,  according to above.
    
    '   Move UP one Row, and repeat,  Until all completed, or until error
    '   (Several rows at top will be headings - therefore NOT be blank, or 0 thru 6)

End Sub





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!

VB:

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




Hi

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:
VB:

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

Hi,

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

Thanks,

---------------------------------

Range("B1").Select
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)
Else
.IndentLevel = (ActiveCell.Offset(0, 8))
End If
End With


Hi All

I have a table that contains the following infromation in A2:FT6010
I need to lookup the information in FT witch is column 175 that matches critiera from A (date) and Name

If 1/2/05 and Kerry = 94.96%
EG
A[INDENT ]B[INDENT ]FT
1/02/2005[indent] Mathew[indent]100.00%
1/02/2005[indent] Kelly[INDENT ]98.25%
1/02/2005[indent] Paul[INDENT ]92.12%
1/02/2005[indent] Kerry[INDENT ]94.96%
1/02/2005[INDENT ]Rick[INDENT ]98.18%
2/02/2005[indent] Mathew[INDENT ]99.85%
2/02/2005[/NDENT ]Kelly[INDENT ]75.12%

Thanks


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

VB:

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

Thanks!

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.

Cheers

Gerry

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'
VB:

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

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

Code:

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
            Else
                MsgBox "DIP not found!"
            End If
        'End If
    End If
End Sub





Hi,

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?


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:
VB:

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


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?



Hi, I got a code from http://www.ozgrid.com/forum/showthread.php?t=61670 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

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)
    MySelection.Select
     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."
        Range("A13").Select
        Exit Sub
    End If
    Ans = MsgBox(msg, Config, Title)
    If Ans = vbYes Then
        MySelection.EntireRow.ClearContents
        Range(Cells(ab,2),Cells(37,4)).Select
        Selection.Copy
        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.

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

VB:

Sub ListThem() 
    Dim startRange As Range 
     
    Sheet1.Cells.Clear 
    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

Regards

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

John




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

Do
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

Loop

Cells(r, c).Select

MsgBox "Finished"

End Sub





Hello everyone.

This is my first post here. I've attempted to find the answer in previous
posts but can. Any help would be really appreciated.

I have 5 colums of results:

AZ2
BA2
BB2
BC2
BD2

I have 6 possible grades:

For 100-92..."Level 6"
For 91 - 79..."Level 5"
For 78 - 70..."Level 4"
For 69 - 50..."Level 3"
For 49 - 34..."Level 2"
Below 34......."Level1"

I need the output to be a Level based on the lowest result from each column.

I thought I could use the IF & OR function for this, but as far as I know it
works for only 2 types of results. Any help on what I could use to expand
this to include all the levels mentioned above?
:
This works...but just for 2 possible results!
=IF(OR(AZ2<=69,BA2<=69,BB2<=69,BD2<=69),"Level 3","Level 4")

Thanks in advance,
Phil