Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



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




View Answers     

Similar Excel Tutorials

Return the Max Number from a Range That is Within a Minimum and Maximum Target Value in Excel - UDF
Return the highest number between two numbers that is in a range of cells with this UDF (user defined function) in ...
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 ...
Determine if a Cell Contains a Function in Excel - Great for Conditional Formatting and Validation - UDF
Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function r ...
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 ...

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?

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




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




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




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

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.


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


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





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


Hi,

Part of my spreadsheet has a command button to action a change of cell colour and also put a number in the cell. This is all dependent on the user that has logged in having the authorisation to make this change. I have written the VBA script for this which works but what I want the VBA code also to do is only make the changes if the selected cells (the selection will often be more than just a single cell) ALL lie within a specific range of cells.

This is what I have got so far which does as I wish as long as all of the selected cells are outside of the range. As soon as some of the selected cells are in the range and others are out it still proceeds to change ALL of the selected cells. The cells are already protected because I don't want any user to change cell information. Can anyone help?

Code:

Private Sub CommandButton2_Click()
If Intersect(Selection, Range("$D$5:$O$35")) Is Nothing Then
Exit Sub
End If
If Sheets("Dashboard").Range("A2") = "janebutl" Then
ActiveSheet.Unprotect Password:="password"
Selection.Interior.ColorIndex = 3
Selection = "1"
End If
Exit Sub
End Sub


Thanks


I am using Excel 2003 on a Windows 2000 machine.

I am having problems when we indent subheadings in Excel. We place our
subheadings in the leftmost column of our excel sheets. To set the
indentation in our subheadings we use the increase indent, and decrease
indent buttons in the menu bar.

We would like to know how much space is indented for each click of the
indent button in the menu bar.
Is this amount of indentation dependent on the font in the cell being
indented. My specific problem is that I am trying to set up the
indents to 2en, 3en, and 5en spaces using the Courier New font. Is
there any way to set up the indent button to do this?
Any feedback would be greatly appreciated.




Hi,

My question is simple but I'm not sure if it's possible. Basically, I have tons of rows in Column A, and some cells say "Sample" and others say " Sample" but instead of a space before the "S," there is a left indent. All these indented cells are also left justified, whereas the first kind of cells (the ones that just say "Sample" with no indent) are not left justified.

It'd be far too much work to manually go through thousands of rows and label each row that had an indented " Sample." Is there any way to label these indented/justified cells (highlight them, replace them with some other text, anything)?

Thanks a lot. Let me know if you have any questions.

-George


Hi,

Part of my spreadsheet has a command button to action a change of cell colour and also put a number in the cell. This is all dependent on the user that has logged in having the authorisation to make this change. I have written the VBA script for this which works but what I want the VBA code also to do is only make the changes if the selected cells (the selection will often be more than just a single cell) ALL lie within a specific range of cells.

This is what I have got so far which does as I wish as long as all of the selected cells are outside of the range. As soon as some of the selected cells are in the range and others are out it still proceeds to change ALL of the selected cells. The cells are already protected because I don't want any user to change cell information. Can anyone help?

Private Sub CommandButton2_Click()
If Intersect(Selection, Range("$D$5:$O$35")) Is Nothing Then
Exit Sub
End If
If Sheets("Dashboard").Range("A2") = "janebutl" Then
ActiveSheet.Unprotect Password:="password"
Selection.Interior.ColorIndex = 3
Selection = "1"
End If
Exit Sub
End Sub


Is there a command that will indent a number in a cell a specified number of indents?

For example:
If there is a 0 in a cell, then no indent, if there is a 1 in a cell then indent 1 space, if there is a 3 in the cell, then indent 3 spaces, etc. So if you were to look at a colum with this formatting it would look like this (without the underscores)

0
_1
__2
__2
___3
__2
___3
____4
_1


for this matter, i want to delete the data from another sheet where activeCell is refer to DataID (in worksheet "Pond" column "3")

Code:

Private Sub cmbDelete_Click()
    Dim msgResponse As String    'confirm delete
    Application.ScreenUpdating = False
    'get user confirmation
    msgResponse = MsgBox("This will delete the selected record. Continue?", _
                         vbCritical + vbYesNo, "Delete Entry")
    Select Case msgResponse    'action dependent on response
        Case vbYes
            'c has been selected by Find button
            Set c = ActiveCell
            c.EntireRow.Delete
        Case vbNo
            Exit Sub    'cancelled
    End Select
End Sub


so everytime time user delete data from main page, the data for particular ID would be deleted at the same time..


Is there any way to indent the contents of a cell from the top or bottom?
The help file states that you are able to achieve this using Format Cells,
Alignment, Vertical, and then selecting Top, Indent or Bottom, Indent under
Vertical Alignment.

The only indent options are under Horiztonal Alignment for Left and Right
Indents.



All, I am having trouble with the following code and I feel like it should be an easy fix. The code works fine if the user has selected cells within a single row. However, when multiple rows are selected, the 'With ActiveCell' formatting only works for the first row selected. How can I loop the final 'else' statement to format the ranges for all selected rows?

Code:

Private Sub Button_DeleteRow_Click()
    
    ActiveSheet.Unprotect Password:=PSWD
        
    Selection.EntireRow.Interior.ColorIndex = 3
    
    msg1 = MsgBox("Delete this row?", vbYesNo)
    If msg1 = vbYes Then
    
    Selection.EntireRow.Delete
    
    Else

    ScreenUpdating = False

    Selection.EntireRow.Interior.ColorIndex = xlNone
       
        With ActiveCell
        Range(Cells(.Row, "AA"), Cells(.Row, "AN")).Interior.ColorIndex = 15
        Range(Cells(.Row, "c"), Cells(.Row, "d")).Interior.ColorIndex = 15
        Range(Cells(.Row, "a"), Cells(.Row, "a")).Interior.ColorIndex = 15
        Range(Cells(.Row, "j"), Cells(.Row, "j")).Interior.ColorIndex = 15
        Range(Cells(.Row, "n"), Cells(.Row, "n")).Interior.ColorIndex = 15
        End With

    End If
    Call ProtectSheet
End Sub





Hey -
Somewhat newbie to VBA - Can anyone tell me what is wrong with this code?
Error is "Object doesn't support this method". Thanks a ton.
Steve

Sub CheckBoxChange()
If ActiveSheet.CheckBox2.Value = True Then
If MsgBox("Do you really want to change the risk level?", _
vbQuestion + vbYesNo) = vbYes Then
ActiveSheet.CheckBox2.Value = False
End If
ElseIf ActiveSheet.CheckBox2.Value = False Then
If MsgBox("Do you really want to change the risk level?", _
vbQuestion + vbYesNo) = vbYes Then
ActiveSheet.CheckBox2.Value = True
End If
End If
End Sub


Is it possible to clear the format in word but WITHOUT clearing italics, bold, etc?

I have this document with mixed "styles." (How it got that way I don't know). One is the typical "Normal: First line indent of .5" and double space." Another is a "Body Text Indent" with a first line indent of .5" and double space. Then I have these "postbody1" styles that are really messing me up.

I had this document edited and it got sent to me by someone else. My guess is they went through and did a "select all" and changed all the font sizes to 12. Because the "postbody1" style is font size 9. How do I know this? Because when I copy and paste the whole document into another document, the font goes to 9 (where the style is "postbody1" goes to 9; the rest of the document remains at 12 font).

I could just go through and hit "select all" and change the font to 12 again after c/p. I am worried however that this "postbody1" will give me some other problems unless I change it. Also, I am worried that if I send this document electonically to someone, it will revert to the font size of 9 (it may not do that seeing as I got sent the document and it did not go back to 9).

The only way to change it from "postbody1" to a "normal" style is by hitting "clear formatting" and then I can change it. Just clicking on the text and hitting the "normal" style does nothing; it remains as "postbody1." The problem with the clear formatting is it clears out all formatting including italics and this is most definitely not what I want.

Help is appreciated!


I have the following code within a macro:

Code:

Dim iRsp%: iRsp = MsgBox(sMsg, vbQuestion + vbYesNo, sID & " :please confirm")
CheckOneClick = (iRsp = vbYes)


Ideally I would like to skip the confirmation messgage and carry on working. If I remove this section it comes up with an error, is there a way I can edit this so that it automatically assumes 'Yes'?

Thanks in advance for any help,

Phil