Can some one be kind enough to help me incorporate this two vba scripts together?

[tazmtiger]

Hi!

I have the following code in my workbook, which works and have no issues with it, but I need to incorporate a second code to it, but can't figure out how to do it?

My current code is:

***********************************************************
Private Sub Worksheet_Change(ByVal Target As Range)
Sheet1.Unprotect Password:="secret"
Const WS_RANGE As String = "B8:B18" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
.Interior.ColorIndex = xlColorIndexNone

Select Case .Value

Case "00": .Interior.ColorIndex = 48 'Light Gray
Case "01": .Interior.ColorIndex = 6 'Bright Yellow
Case "02": .Interior.ColorIndex = 46 'Orange
Case "03": .Interior.ColorIndex = 3 'Red
Case "04": .Interior.ColorIndex = 7 'pink
Case "05": .Interior.ColorIndex = 39 'Light Violet
Case "06": .Interior.ColorIndex = 33 'Light Blue
Case "07": .Interior.ColorIndex = 33 'Light Blue
Case "08": .Interior.ColorIndex = 35 'light green
Case "09": .Interior.ColorIndex = 33 'Light Blue
Case "10": .Interior.ColorIndex = 19 'Light Tan
Case "11": .Interior.ColorIndex = 48 'light gray
Case "12": .Interior.ColorIndex = 6 'Bright Yellow
Case "13": .Interior.ColorIndex = 46 'Orange
Case "15": .Interior.ColorIndex = 45 'Light Orange
Case "14": .Interior.ColorIndex = 2 'White
Case "16": .Interior.ColorIndex = 3 'Red
Case "18": .Interior.ColorIndex = 3 'Red
Case "22": .Interior.ColorIndex = 45 'Orange
Case "20": .Interior.ColorIndex = 46 'Orange
Case "17": .Interior.ColorIndex = 2 'White
Case "26": .Interior.ColorIndex = 39 'Light violet
Case "27": .Interior.ColorIndex = 39 'Light Violet
Case "19": .Interior.ColorIndex = 27 'Light Violet

'etc.

End Select '<-- added
End With
End If
ws_exit:
Application.EnableEvents = True
Sheet1.Protect Password:="secret"
End Sub

*************************************************************

The next code I need to incorporate together is:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Dim c, d As Range
Set d = Intersect(Target, Range("B25:B39"))
If d Is Nothing Then
Else
If Len(Target) > 105 Then

Target.Offset(1, 0) = Right(Target, Len(Target) - 105) & Target.Offset(1, 0)
Target = Left(Target, 105)

Target.Offset(1, 0) = Right(Target, 105 - Application.WorksheetFunction.Find("^^", Application.WorksheetFunction.Substitute(Target, " ", "^^", Len(Target) - Len(Application.WorksheetFunction.Substitute(Target, " ", ""))))) & Target.Offset(1, 0)
Target = Left(Target, Application.WorksheetFunction.Find("^^", Application.WorksheetFunction.Substitute(Target, " ", "^^", Len(Target) - Len(Application.WorksheetFunction.Substitute(Target, " ", "")))))
End If
End If
End Sub

********************************************************
Can some one help me put it together? Thank you very much in advance.

 

[tazmtiger]

Any One????

Perhaps if I explain the purpose for both codes, it my help get my answer easier in the process???

One code recalls information based on data typed. The other limits the amount of characters typed in a line, by moving any excess characters to a new line.

Both work great independently, and have no issues running by them selves. My problem is that both codes use the --> Private Sub Worksheet_Change(ByVal Target As Range) <-- format and excel won't let me use both without creating some kind of mix format. And I am not talented enough to understand how to incorporate both together. Perhaps some one can assist me in the process.

Please! Help! Thank you!

 

[tazmtiger]

I think I figure it out? Hopely it won't bug out on me later?

Here is how I place them together.
Seems to be working fine...
I hope I don't run into compile errors eventually.

Thank you guys anyway.. And by the way, your help is always very appreciated.
I love you folks!




Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Dim c, d As Range
Set d = Intersect(Target, Range("b24:b39"))
If d Is Nothing Then
Else
If Len(Target) > 105 Then
'First we add the spillover to the second cell and then truncate the first cell

Target.Offset(1, 0) = Right(Target, Len(Target) - 105) & Target.Offset(1, 0)
Target = Left(Target, 105)

'Now we have to add spillover to the second cell = the last bit of a word
'then we will remove that word from the first cell

Target.Offset(1, 0) = Right(Target, 105 - Application.WorksheetFunction.Find("^^", Application.WorksheetFunction.Substitute(Target, " ", "^^", Len(Target) - Len(Application.WorksheetFunction.Substitute(Target, " ", ""))))) & Target.Offset(1, 0)
Target = Left(Target, Application.WorksheetFunction.Find("^^", Application.WorksheetFunction.Substitute(Target, " ", "^^", Len(Target) - Len(Application.WorksheetFunction.Substitute(Target, " ", "")))))
End If
End If
'End Sub

Sheet1.Unprotect Password:="secret"

Const WS_RANGE As String = "B8:B18" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
.Interior.ColorIndex = xlColorIndexNone

Select Case .Value

Case "00": .Interior.ColorIndex = 48 'Light Gray
Case "01": .Interior.ColorIndex = 6 'Bright Yellow
Case "02": .Interior.ColorIndex = 46 'Orange
Case "03": .Interior.ColorIndex = 3 'Red
Case "04": .Interior.ColorIndex = 7 'pink
Case "05": .Interior.ColorIndex = 39 'Light Violet
Case "06": .Interior.ColorIndex = 33 'Light Blue
Case "07": .Interior.ColorIndex = 33 'Light Blue
Case "08": .Interior.ColorIndex = 35 'light green
Case "09": .Interior.ColorIndex = 33 'Light Blue
Case "10": .Interior.ColorIndex = 19 'Light Tan
Case "11": .Interior.ColorIndex = 48 'light gray
Case "12": .Interior.ColorIndex = 6 'Bright Yellow
Case "13": .Interior.ColorIndex = 46 'Orange
Case "15": .Interior.ColorIndex = 45 'Light Orange
Case "14": .Interior.ColorIndex = 2 'White
Case "16": .Interior.ColorIndex = 3 'Red
Case "18": .Interior.ColorIndex = 3 'Red
Case "22": .Interior.ColorIndex = 45 'Orange
Case "20": .Interior.ColorIndex = 46 'Orange
Case "17": .Interior.ColorIndex = 2 'White
Case "26": .Interior.ColorIndex = 39 'Light violet
Case "27": .Interior.ColorIndex = 39 'Light Violet
Case "19": .Interior.ColorIndex = 27 'Light Violet

'etc.
End Select '<-- added
End With
End If

ws_exit:
Application.EnableEvents = True
Sheet1.Protect Password:="secret"
End Sub