Excel：具有2个相同的工作表，将文本添加到1并使其显示在相同的工作表中 [英] Excel: Having 2 identical sheets, adding text to 1 and having it show up in the identical sheet

问题描述

您好，

我的工作簿中有两张相同的表格。我想在一张纸上键入注释，并将其自动显示在第二张纸中。

I have two identical sheets in my workbook. I would like to type a comment into one sheet and have it automatically appear in the second sheet.

例如：

如果我在工作表1的单元格A1中键入HELLO ，它应该自动出现在工作表2的单元格A1中。

If I type HELLO in cell A1 in Sheet 1, it should automatically appear in cell A1 in Sheet 2.

最重要的是，我希望工作表2能够自动翻译成法语。

To top it al off, I would like Sheet 2 to automatically translate into French.

工作簿正在被许多人使用，因此受到很大保护。我希望能够锁定我用来完成这项任务的任何功能或工具。

The workbook is being used by many people, and therefore is quite protected. I would like to be able to lock any of the functions or tools I use to get this done.

有什么想法吗？谢谢！

推荐答案

您好
Lisas3939，

你曾提到过，"如果我在工作表1的单元格A1中键入HELLO ，它应自动出现在工作表2的单元格A1中。"

you had mentioned that,"If I type HELLO in cell A1 in Sheet 1, it should automatically appear in cell A1 in Sheet 2. "

以达到上述要求，使用下面的代码。

to achieve above requirement use code below.

将此代码放在工作表1中模块。

place this code in Sheet 1 module.

Private Sub Worksheet_Change(ByVal Target As Range)

Static pPrevious As Range
    
    Set pPrevious = Target
Set PreviousActiveCell = pPrevious
'Debug.Print (PreviousActiveCell)

Sheets(2).Cells(PreviousActiveCell.Row, PreviousActiveCell.Column).Value = PreviousActiveCell.Value
End Sub

你提到过的其他事情，"最重要的是，我希望Sheet 2自动翻译成法语"

other thing you had mentioned that,"To top it al off, I would like Sheet 2 to automatically translate into French. "

要达到上述要求，请使用以下代码。

to achieve above requirement use code below.

将此代码放入工作表  2模块。

place this code in Sheet 2 module.

Private Sub Worksheet_Change(ByVal Target As Range)
 Dim s As String
    s = Target.Value
    
Target.Value = transalte_using_vba(s)
End Sub

创建一个新模块并粘贴下面的代码。

create one new module and paste code below.

 Function transalte_using_vba(str) As String
' Tools Refrence Select Microsoft internet Control


    Dim IE As Object, i, j As Long
    Dim inputstring As String, outputstring As String, text_to_convert As String, result_data As String, CLEAN_DATA

    Set IE = CreateObject("InternetExplorer.application")
    '   TO CHOOSE INPUT LANGUAGE

    inputstring = "auto"

    '   TO CHOOSE OUTPUT LANGUAGE

    outputstring = "fr"

    text_to_convert = str

    'open website

    IE.Visible = False
    IE.navigate "http://translate.google.com/#" & inputstring & "/" & outputstring & "/" & text_to_convert

    Do Until IE.ReadyState = 4
        DoEvents
    Loop

    Application.Wait (Now + TimeValue("0:00:5"))

    Do Until IE.ReadyState = 4
        DoEvents
    Loop

    CLEAN_DATA = Split(Application.WorksheetFunction.Substitute(IE.Document.getElementById("result_box").innerHTML, "</SPAN>", ""), "<")

    For j = LBound(CLEAN_DATA) To UBound(CLEAN_DATA)
        result_data = result_data & Right(CLEAN_DATA(j), Len(CLEAN_DATA(j)) - InStr(CLEAN_DATA(j), ">"))
    Next


    IE.Quit
    transalte_using_vba = result_data


End Function

当你尝试在sheet1中输入一些数据时自动复制到sheet2然后它将使用谷歌翻译翻译成法语。翻译可能需要1或2秒。

when you try to enter some data in sheet1 the data get automatically copied to sheet2 then it will translated to French using Google Translate. it may take 1 or 2 seconds for translation.

确保您已与互联网连接。

make sure that you are connected with internet.

输出：

问候

Deepak

这篇关于Excel：具有2个相同的工作表，将文本添加到1并使其显示在相同的工作表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！