Before you proceed to sign up,
Please select one of the following!
I want to Hire
I want to Work as an
Enter Password
Verify Password

Customizing xls template for my translator using VB etc

IT & Programming > Scripts & Utilities

Posted: 25th Dec 2016
TIME FRAME:Less than 1 Week
START:25th Dec 2016

Job Description

I am working on some subtitling and translation projects where I have to get the English Language scripts to be translated in various languages.
For this there are a couple of criteria to be followed like

1)Each line of translation should not exceed 38 charterers in length

2)There should not be any word typed in English in the translation

As of now I have a VB script which limits the defined cell to 38 charterers and automatically shifts any thing above that to the next right cell.But it cuts any thing above 38 characters so even a word gets split. For example in the sentence "how are you I have not seen you for lo" "ng" the word "long" is split after "lo" because the limit of 38 charterers end there. i want to alter the code so that it splits the sentence after a whole word. Following is the code i am using:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim str_len As Long
Dim str_txt As String
Dim desired_len As Long
Dim range1 As Range
Dim range2 As Range

Set range1 = ActiveSheet.Range("d2:d30")

If (Not Intersect(range1, Target) Is Nothing) Or Not (Intersect(range1, Target) Is Nothing) Then
str_txt = Target.Value

desired_len = 38
str_len = Len(str_txt)

If str_len > desired_len Then
Target.Value = Left(str_txt, desired_len)
Target.Offset(0, 1).Value = Right(str_txt, str_len - desired_len)

End If

End If

End Sub

Is it possible ot make a macro to search for a word written in a particular font in a coloumn.

Another thing I am using a Macro for Highlighting Duplicates whole words between two columns of the same sheet, i want to modify it so that i can Highlighting Duplicates whole words between two columns of two different sheets, and how do i define the sheet and columns.

Following is the Code of the Macro

Option Explicit
Sub HighlightDups()
Dim rg1 As Range, rg2 As Range, c As Range, d As Range
Dim sTemp As String, sTempWords As Variant
Dim i As Long, j As Long
Dim sFirstAddress As String

Set rg1 = Range("A1", Cells(Rows.Columns.Count, "A").End(xlUp))
Set rg2 = Range("B1", Cells(Rows.Columns.Count, "B").End(xlUp))

With Range(rg1, rg2)
.Font.Color = vbBlack
.Font.Bold = False
End With

For Each c In rg1
If Len(c.Text) > 0 Then
sTempWords = Split(WorksheetFunction.Trim(c.Text))
For i = 0 To UBound(sTempWords)
Set d = rg2.Find(What:=sTempWords(i), _
LookIn:=xlValues, _
LookAt:=xlPart, _
If Not d Is Nothing Then
sFirstAddress = d.Address
With c.Characters(InStr(1, c.Text, sTempWords(i)), Len(sTempWords(i))).Font
.Color = vbRed
.Bold = True
End With
With d.Characters(InStr(1, d.Text, sTempWords(i)), Len(sTempWords(i))).Font
.Color = vbRed
.Bold = True
End With
Set d = rg2.FindNext(after:=d)
Loop While Not d Is Nothing And d.Address <> sFirstAddress
End If
Next i
End If
Next c
End Sub

Lastly after the above have been incorporated i want to lock the excel template so as any of translators cannot change the rules intentional or unintentional. And it should also not be a problem for them while translating.

And also if i can disable the "Export" and "Save As" function. Only the can save the file as it is and send it back.

Prompt Revert will be appreciated as i am in a bit of hurry with my deliveries
Please Feel free to ask any thing for clarity

Skills Required: Visual Basic
Project Code: IND60645
5.0 average rating
Professional Visual Basic Application Developer and Robotics Process Automation (RPA) - Automation Anywhere developer, expert in Advanced Excel VBA Macros development / V....
Bid Code: 586640
Bid Date: Dec, 25 2016
Looking for work?
Sign Up as a contractor and Start Working today
Sign in to view more jobs and submit a proposal. Once registered with us, you'll have access to many more jobs.
Looking to Hire?
Post Your Project Today!
Hiring on Work N Hire is fast. Create your project posting and publish it to thousands of contractors to get proposals in no time.