Excel Macro (Excel XP)

Topic

New Reply

I have 6 spreadsheets with all the same information (campus specific). What I would like to do is in a column that corresponds with the campus create “boxes” with borders. I’m not quite sure how to write the code. Can some savy VB expert help me?

Thanks

Racheal

Reply | Quote

Replies

I’m not sure exactly what you want to do. The code in This Post shows how to put a border around a cell.

Reply | Quote

(Edited by HansV – inserted

 and 

tags to preserve spacing – see Help 19)

PHI	120						
		Community	 	X	 	 	
		Desert Vista	 	X	 	 	
		Downtown	X	X	X	 	
		East	 	 	 	X	
		Northwest	 	 	 	X	
		West	X	X	X	 	
PHI	122						
		Community	 	 	 	X	
		Desert Vista	X	X	X	 	
		Downtown	X	 	 	 	
		East	 	 	 	X	
		Northwest	 	 	 	X	
		West	 	X	 	 	
PHI	123						
		Community	 	 	 	X	
		Desert Vista	X	X	X	 	
		Downtown	 	 	 	X	
		East	 	 	 	X	
		Northwest	 	 	 	X	
		West	X	X	 	 	

Above is part of the spread sheet layout

Where it has the specific campuses I wanted to create “boxes” on that campus line but in another set of columns. I hope this clarifies better what I want

Thanks for any and all of your help.

Racheal

Reply | Quote

It is not clear to me what you want. Could you elaborate some more?

What do you mean by “boxes”?
Steve

Reply | Quote

It is not clear to me what you want. Could you elaborate some more?

What do you mean by “boxes”?
Steve

Reply | Quote

First, it would be much easier if you would upload a workbook with the data in it. That way we could see what actual columns and rows the data is actually in and we could use that to test the VBA code to do what you are asking.

What you are asking is still not very clear. What is a “campus?” Is it PHI or 120 or Community. I would guess that it is Community which I would guess is in Column C, but could be column B or D or E or … What other “set of columns” do you want boxes? The columns with the X’s in them? If so, do you want boxes around all of the columns with X;s in them? What columns are the X’s in? If not the columns with the X’s, then what columns? Is it OK to make the “boxes” by putting borders around the cells, or are you looking for something else. Could you define what a box is?

Reply | Quote

First, it would be much easier if you would upload a workbook with the data in it. That way we could see what actual columns and rows the data is actually in and we could use that to test the VBA code to do what you are asking.

What you are asking is still not very clear. What is a “campus?” Is it PHI or 120 or Community. I would guess that it is Community which I would guess is in Column C, but could be column B or D or E or … What other “set of columns” do you want boxes? The columns with the X’s in them? If so, do you want boxes around all of the columns with X;s in them? What columns are the X’s in? If not the columns with the X’s, then what columns? Is it OK to make the “boxes” by putting borders around the cells, or are you looking for something else. Could you define what a box is?

Reply | Quote

Attached is a sample of the worksheet and a comment explaining what I want to do.

Racheal

Reply | Quote

Still not entirely clear.
Try a guess: Would cond formatting work?

Select (eg) D4: G whatever
Format – cells – Border(tab) -none
Format – conditional formatting
select from list: FormulaIs
Enter the formula:
=$C4″”

Border(tab) – press “solid line” and “outline” button

Steve

Reply | Quote

I probably followed your directions incorrectly as it did nothing.
Racheal

Reply | Quote

What do you mean by “nothing”

It should pull up several dialog boxes at least
Dialog1(Formatting)
Initially it will”clear” the borders
dialog2 (cond formatting)
Then it will add borders around the cells that selected that have something in Cell C4

What step fails?

Steve

Reply | Quote

I selected the cell that I wanted with the borders and followed your steps. After I hit ok the cell still didn’t have borders. If there has to be text in the cell then it will not work for what I want. Thanks
Racheal

Reply | Quote

There just has to be text in column C of that row to add the borders.

Could you attach the demo sheet where you followed the directions?
Steve

Reply | Quote

I will try it again but I will put a header on the column. If that doesn’t work then I will forward you a copy.
Thanks again
Racheal

Reply | Quote

I will try it again but I will put a header on the column. If that doesn’t work then I will forward you a copy.
Thanks again
Racheal

Reply | Quote

There just has to be text in column C of that row to add the borders.

Could you attach the demo sheet where you followed the directions?
Steve

Reply | Quote

I selected the cell that I wanted with the borders and followed your steps. After I hit ok the cell still didn’t have borders. If there has to be text in the cell then it will not work for what I want. Thanks
Racheal

Reply | Quote

What do you mean by “nothing”

It should pull up several dialog boxes at least
Dialog1(Formatting)
Initially it will”clear” the borders
dialog2 (cond formatting)
Then it will add borders around the cells that selected that have something in Cell C4

What step fails?

Steve

Reply | Quote

I probably followed your directions incorrectly as it did nothing.
Racheal

Reply | Quote

Still not entirely clear.
Try a guess: Would cond formatting work?

Select (eg) D4: G whatever
Format – cells – Border(tab) -none
Format – conditional formatting
select from list: FormulaIs
Enter the formula:
=$C4″”

Border(tab) – press “solid line” and “outline” button

Steve

Reply | Quote

See if this does what you want:

Public Sub InsertBorders()
Dim I As Long, lLastRow As Long
    With Worksheets("Sheet1")
        lLastRow = .Range("C65536").End(xlUp).Row - 1
        For I = 3 To lLastRow
            If .Range("C1").Offset(I, 0).Value  "" Then
                With .Range("D1:G1").Offset(I, 0).Borders
                    .LineStyle = xlContinuous
                    .Weight = xlThin
                    .ColorIndex = xlAutomatic
                End With
            End If
        Next I
    End With
End Sub

Reply | Quote

Did it work for you?
I tried to cut and past the code but it’s not working

Racheal

Reply | Quote

I think I still did not understand what you wanted. I was putting borders around the cells in columns D:G. Looking at it again, I think you wanted the borders around the cells in I:L. Try the code below. If you want to put borders around both sets of columns, then uncomment the commented code.

Public Sub InsertBorders()
Dim I As Long, lLastRow As Long
    With Worksheets("Sheet1")
        lLastRow = .Range("C65536").End(xlUp).Row - 1
        For I = 3 To lLastRow
            If .Range("C1").Offset(I, 0).Value  "" Then
'                With .Range("D1:G1").Offset(I, 0).Borders
'                    .LineStyle = xlContinuous
'                    .Weight = xlThin
'                    .ColorIndex = xlAutomatic
'                End With
                With .Range("I1:L1").Offset(I, 0).Borders
                    .LineStyle = xlContinuous
                    .Weight = xlThin
                    .ColorIndex = xlAutomatic
                End With
            End If
        Next I
    End With
End Sub

Reply | Quote

I think I still did not understand what you wanted. I was putting borders around the cells in columns D:G. Looking at it again, I think you wanted the borders around the cells in I:L. Try the code below. If you want to put borders around both sets of columns, then uncomment the commented code.

Public Sub InsertBorders()
Dim I As Long, lLastRow As Long
    With Worksheets("Sheet1")
        lLastRow = .Range("C65536").End(xlUp).Row - 1
        For I = 3 To lLastRow
            If .Range("C1").Offset(I, 0).Value  "" Then
'                With .Range("D1:G1").Offset(I, 0).Borders
'                    .LineStyle = xlContinuous
'                    .Weight = xlThin
'                    .ColorIndex = xlAutomatic
'                End With
                With .Range("I1:L1").Offset(I, 0).Borders
                    .LineStyle = xlContinuous
                    .Weight = xlThin
                    .ColorIndex = xlAutomatic
                End With
            End If
        Next I
    End With
End Sub

Reply | Quote

Did it work for you?
I tried to cut and past the code but it’s not working

Racheal

Reply | Quote

See if this does what you want:

Public Sub InsertBorders()
Dim I As Long, lLastRow As Long
    With Worksheets("Sheet1")
        lLastRow = .Range("C65536").End(xlUp).Row - 1
        For I = 3 To lLastRow
            If .Range("C1").Offset(I, 0).Value  "" Then
                With .Range("D1:G1").Offset(I, 0).Borders
                    .LineStyle = xlContinuous
                    .Weight = xlThin
                    .ColorIndex = xlAutomatic
                End With
            End If
        Next I
    End With
End Sub

Reply | Quote

Attached is a sample of the worksheet and a comment explaining what I want to do.

Racheal

Reply | Quote

Many people know you can group sheets by control or shift-clicking their tabs and that after doing that any (formatting) change made to the active sheet are reflected to all grouped sheets.

Very few people know you can do the same for multiple workbooks too.

– Insert a blank workbook, open the VBE (alt-F11) and paste in this code:

Sub groupworkbooks()
Application.Dialogs(xlDialogWorkgroup).Show
End Sub

Now exit the VBE.

– Open all files you need to group
– now hit alt-F8 and run the macro called Groupworkbooks
– select the sheets in the list you wish to group and hit OK (by default all sheets are selected, unselect the ones you wish NOT to group)
– You will see [Group] appear in the title bar.

Start modifying your sheet.

Note: As soon as you change to another worksheet or workbook, the grouping is cancelled.

Reply | Quote

Nifty!
And is this only available through the VBE?

Reply | Quote

Yes, I haven’t been able to do it otherwise.

Reply | Quote

It makes you wonder what other Dialogs and functionality is hidden deep in the recesses of VBA???
I should have a closer look at the Dialogs() feature!

Reply | Quote

Good luck, there’s 240 of them in Excel XP….

Reply | Quote

I’ll scan through those I don’t recognise! As most are common in the menus

Reply | Quote

I’ll scan through those I don’t recognise! As most are common in the menus

Reply | Quote

Good luck, there’s 240 of them in Excel XP….

Reply | Quote

It makes you wonder what other Dialogs and functionality is hidden deep in the recesses of VBA???
I should have a closer look at the Dialogs() feature!

Reply | Quote

I’d like to control this grouping function without showing the dialog box. I already know what files I’d like to group.
Any suggestions?

Reply | Quote

Jan Karel is not available at the moment.

As far as I know, the dialog is the only way the workbook grouping feature is exposed. If you want to group ALL workbooks/worksheets, you can use

Sub GroupAll()
SendKeys “~”
Application.Dialogs(xlDialogWorkgroup).Show
End Sub

Reply | Quote

Hi Catherine,

I could not find a way to do this, apart from using the dialog.

Reply | Quote

Yes, I haven’t been able to do it otherwise.

Reply | Quote

Nifty!
And is this only available through the VBE?

Reply | Quote

Jan Karel:

Thanks for that – very useful.

Do you have any other similar hidden gems?

Tony.

Reply | Quote

Well, RudiS listed a couple here

Reply | Quote

Well, RudiS listed a couple here

Reply | Quote

Jan Karel:

Thanks for that – very useful.

Do you have any other similar hidden gems?

Tony.

Reply | Quote