Concatenate issue

[Daniej]

I really need help. I need to put together a spreadsheet for my boss. I do not know VBA (at all - someone helped with the macro below)

I have several issues.

1. Using the formula below in the Master sheet, I need to bold the name of the sites (Big Spring, Dubois, etc) when it returns a result. How can I do this?


=CONCATENATE("Big Spring: ", 'Big Spring'!AA3, "; ", "DuBois: ", DuBois!AA3, "; ", "Houston - FWP: ", 'Houston - FWP'!AA3, "; ", "Savage-Ames: ", 'Savage-Ames'!AA3, "; ", "Sayre: ", Sayre!AA3, "; ","Trenton Pipe Yard: ", 'Trenton Pipe Yard'!AA3, "; ", "Trenton-EMI: ", 'Trenton-EMI'!AA3, "; ", "Williston: ", Williston!AA3)


2. In each sheet from the above example (in columns AA, AB, AC) there is existing instructional text (that disappears and changes color when new text has been entered (Done in VBA - see below). I want the above formula to extract when new text has been entered. (I only want the concatenated results to return information if someone has changed the information in cells AA, AB, AC.). How do I do this?

VBA code
Private Sub Worksheet_Change(ByVal Target As Range)
'
If Target.Column = 27 Then
Target.Font.Color = Automatic
End If
'
If Target.Column = 28 Then
Target.Font.Color = Automatic
End If
'
If Target.Column = 29 Then
Target.Font.Color = Automatic
End If
'
Target.Interior.Color = xlNone
End Sub




3. How do I format the information so that when it is returned each comment is on a separate line within the cell.
I am at my wits end trying to figure this out... Can anyone help?

Danielle

 

[Daniej]

Re: I have a concatenate issue. Can anyone help?

Hi Eric,

It works except the color in the "location" sheet turns to: RGB (252, 242, 242). Any ideas?

 

[Eric W]

Re: I have a concatenate issue. Can anyone help?

Are you talking about the Font color or the Interior color? You could change this line:

Target.Font.Color = xlAutomatic

to

Target.Font.Color = Automatic

That would match your original macro. If that still doesn't do it, you can change these lines

Target.Font.Color = xlAutomatic
Target.Interior.Color = xlNone

to

Target.Font.Color = RGB(10, 20, 30)
Target.Interior.Color = RGB(40, 50, 60)

where you set the RGB values to what you want.

 

[Daniej]

Re: I have a concatenate issue. Can anyone help?

Eric - you are a miracle worker!

 

[Eric W]

Re: I have a concatenate issue. Can anyone help?

Glad it works for you!

 

[Daniej]

Re: I have a concatenate issue. Can anyone help?

Eric

Your code works great! Thank you...

That said, I am now having a problem when I protect the sheets. It gives me an error message at Ln25, Col22 (.Value = str1)

The error I am getting is:

Run-time error '1004':
The cell or chart you're trying to change is on a protected sheet.

To make changes, click unprotect sheet in the review tab (you might need a password).

Is there anyway I can protect the sheets (and the master)?

If I were to send just the one sheet to its location - and then copy it into the master. Would that work? Would I have to put the VBA code after I copy the sheet back into the master or can I send it out (one sheet to its location) with the VBA code?

Thanks for all your help Eric... you are getting me a lot of points with my boss!

 

[Eric W]

Re: I have a concatenate issue. Can anyone help?

How are you protecting the sheets? Manually from the Review tab? Easiest way is to select columns AA:AC on your Master tab, Right click > Format Cells > Protection > and uncheck the Locked box. Then protect the sheet. Everything else will be locked on that sheet, but it will allow the macro to update those columns.

If those are cells that you don't want people to change manually, then you'll need to protect it, and then put code in your macro to unprotect it/protect it again like this:

    ActiveSheet.Unprotect Password:="bobsyouruncle"
    With Sheets("Master").Range(Target.Address)
        .Value = str1
        .Font.Bold = False
        For i = 1 To ctr
            .Characters(Start:=sl(i, 1), Length:=sl(i, 2)).Font.Bold = True
        Next i
    End With
    ActiveSheet.Protect Password:="bobsyouruncle"

But are you protecting from incidental changes, or from someone who wants to deliberately change it? If the former, then you wouldn't really need a password. If the latter, then you'd probably want to hide the password in the code. You'd need to lock your VBA project with yet another password. And even then, password security in Excel is pretty weak, and someone dedicated enough can bypass it.

Brownie points are always nice!

 

[Daniej]

Re: I have a concatenate issue. Can anyone help?

Hi Eric,

I am protecting the sheet manually from the Review tab... I wanted to protect the Master sheet (when I have to send out the complete Workbook) so that no one can change the information in any of the cells.

I was hoping to protect the location sheets (drop down menus) so end-users can't remove or change them and I wanted to protect the first two rows and columns (of each location sheet) that have the job type (rows) and the OSHA regulations in the columns.

 

[Eric W]

Re: I have a concatenate issue. Can anyone help?

Still a little unclear. If you don't want people to manually change the Master sheet, then protect it. If you don't want them to update it via the macro, remove the macros before sending it on. If you want them to be able to change the Master sheet via the macro, then add the Unprotect/Protect lines.

 

[Daniej]

Re: I have a concatenate issue. Can anyone help?

The problem I am running into is when I protect the master sheet the macro doesn't work. I get an error message. In a way I understand this because the sheet is protected. That said, I am wondering why the information won't gather in the master sheet (if it is protected) if it is not an end user changing it.

So I am wondering if I can protect the master and still run this macro?

I know that if I unprotect the Master the macro works great (and I am thankful for that)!

 

[Eric W]

Re: I have a concatenate issue. Can anyone help?

Did you not see post 16? It shows you how to change the macro so it will run when the Master sheet is protected. But if you do that, it won't run properly if the sheet isn't protected. No good answer to that.