Using code that will allow 2 values in same cell, with being

[ABabeNChrist]

I’m able to get both values within the same cell, but I can’t seem to get the first value to be Bold
Here’s an example; the word is “Date:” and the link will come from Sheet2
The results I’m trying to achieve may look like this
Data: April 4, 2010
I trying to have achieve this in a single cell
The link source is on Sheet3
The link target is on Sheet2
And my results on Summary
I have attached a Sample WorkBook. Any suggestions or thoughts would greatly appreciated.

Sample Book1.xlsm

[HansV]

You forgot to refer to Sheet2 in the line that makes the first part bold:

.Range("C3").Characters(1, Len(Sheets("Sheet2").Range("B1").Value)).Font.Bold = True

[ABabeNChrist]

OOPS
Thank You Hans

[ABabeNChrist]

I’m using this same approach but am not able to format the date layout at the target location to
April 16, 2010, it will only format a date to 4/16/2010
I tried changing the format from the source, with no luck

[HansV]

The format of a cell doesn't affect the way its Value is stored. The Text property contains the value as displayed on screen:

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
  With Sheets("Summary")
    .Range("C3").Value = Sheets("Sheet2").Range("B1").Value & ": " & _
      Sheets("Sheet2").Range("B2").Text
    .Range("C3").Characters(1, Len(Sheets("Sheet2").Range("B1").Value)).Font.Bold = True
  End With
End Sub

Alternatively, you can specify the format explicitly in the code. This way, it could even be different from the format of Sheet2!B2:

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
  With Sheets("Summary")
    .Range("C3").Value = Sheets("Sheet2").Range("B1").Value & ": " & _
      Format(Sheets("Sheet2").Range("B2").Value, "mmmm d, yyyy")
    .Range("C3").Characters(1, Len(Sheets("Sheet2").Range("B1").Value)).Font.Bold = True
  End With
End Sub

[ABabeNChrist]

Very cool thank you Hans

[ABabeNChrist]

I made another change to a different part of this workbook using

Code: Select all

  With Sheets("Summary")
        .Range("A2").Value = "Date of Inspection:  " & _
         Sheets("Client_info").Range("E6").Text
  End With
  
  With Sheets("Summary")
        .Range("A3").Value = "Inspection Address:  " & _
         Sheets("Client_info").Range("B12").Text
  End With

Now if I wish to add a name description like “Date of Inspection “ before the select range text. Is this the correct method and if I were to have let say the name description in bold and the selected range text in regular text.

[HansV]

You can do it like that. To make part of the text bold, you'd use code like this:

Code: Select all

  With Sheets("Summary")
    .Range("A2").Value = "Date of Inspection:  " & _
      Sheets("Client_info").Range("E6").Text
    .Range("A2").Characters(1, Len("Date of Inspection")).Font.Bold = True
  End With

But it would be much easier to place the text "Date of inspection:" in one cell, and make the cell bold, and enter the formula =Client_Info!E6 in the cell next to it.

[ABabeNChrist]

Thank You Hans

But it would be much easier to place the text "Date of inspection:" in one cell, and make the cell bold, and enter the formula =Client_Info!E6 in the cell next to it.

I thought of that but with the layout I have I do not have a cell to do this.

[ABabeNChrist]

I can not seem to get this code to work correctly

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("E6")) Is Nothing Then

        Sheets("Summary").Visible = xlSheetVisible
        Sheets("Summary").Range("A2").Value = "Date of Inspection:  " & _
                Sheets("Client_info").Range("E6").Text
        Sheets("Summary").Range("A2").Characters(1, Len("Date of Inspection:")).Font.Bold = True
  
  End If
        Sheets("Summary").Range("A3").Value = "Inspection Address:  " & _
                Sheets("Client_info").Range("B12").Text
        Sheets("Summary").Range("A3").Characters(1, Len("Inspection Address:")).Font.Bold = True
        Sheets("Summary").Visible = xlSheetHidden

    Application.EnableEvents = False
    If Not Application.Intersect(Target, Range("B6")) Is Nothing Then
        Target(1).Value = UCase(Target(1).Value)
    
End If
    Application.EnableEvents = True

End Sub

Your previous code worked good, but I tried to apply unhide and hide of a sheet when a selected cell has changed

[HansV]

What exactly is the problem? Your code appears to do what it says on the tin if I try it.

(Please try to indent your code consistently)

[ABabeNChrist]

Hi Hans
I was able to get the code to function correctly when I applied it to a different sheet other than that is mentioned within the code. I was trying to attach this code to sheet Client_info so that if any changes were to be made the Summary page would then also change.

[HansV]

The Worksheet_Change of Sheet A will obviously not do anything if a cell on Sheet B is changed, you'd need to use the Worksheet_Change event of Sheet B for that.

If that doesn't help, could you explain exactly which sheets are involved?

[ABabeNChrist]

Hi Hans
I’ll do my best to explain, as you know I have this workbook with mutable sheets within. I use a sheet named Client_info were all main client information is entered. All of the other sheets within this workbook are linked to this sheet except for 1 sheet named Summary, only part of this sheet is linked. This is because of the layout in which I am using I was unable to use the linking method, so that is why I was trying code for the same affect / appearance as with the other sheets. I just do not have that extra cell. So since the Client_info sheet is the main source and if a change is made in the middle of doing a report from Client_info, than it would update this data if needed to the Summary sheet.

[HansV]

Assuming that the code that you posted is the Worksheet_Change event procedure for the Client_Info sheet, it should update the Summary sheet. Can you tell what goes wrong?
- Do you get an error message?
- Do the wrong cells in Summary get updated?
- Does nothing get updated?

And why do you unhide, then hide the Summary sheet?

[ABabeNChrist]

Hi Hans
Here is the error message I am receiving.

ERROR.JPG

on this line

Code: Select all

        Sheets("Summary").Range("A2").Value = "Date of Inspection:  " & _
        Sheets("Client_info").Range("E6").Text

I use hide and unhide a sheet, so that it would update sheet since it was hidden at that momment

[HansV]

I'm afraid that I'll have to see (a stripped down copy of) the workbook.