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?

Yes Eric I saw post 16 and copied it.

When you say "if the sheet isn't protected" you are referring to the master, it will be. Not to be stupid or anything but where does the Macro (from post 16) go? Do I select the master sheet and get the VBA window or do I put it on the individual sheets? If it is the individual sheets, where within the other macro do I put it?

 

[Eric W]

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

I suppose I could have been more clear. The intention from post 16 was to add 2 lines to your existing macro. The 2 lines in red were to be added, the other lines were just a snippet from the macro to show you where to put them. You'd need to do that to each of the macros on all of your location macro sheets.

However, I had another thought that might work better. Leave the location macros alone for now. Add this macro to the ThisWorkbook tab in the editor:

Private Sub Workbook_Open()
    Sheets("Master").Protect Password:="bobsyouruncle", UserInterFaceOnly:=True
End Sub

Change it to your password. What this does is protect the Master sheet whenever the workbook is opened. If it's already protected with the same password, that's OK. The important part is the UserInterFaceOnly parameter. This essentially prevents people from manually making changes, but allows the macros to make changes. It does need to be reset every time you open the workbook.

It is nicer in that you don't have to go change all the macros, and if you change the password, you only need to do it in one place.

 

[Daniej]

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

Awesome Eric... thank you for all your help! It has been a pleasure!