如何编辑通过Excel中的宏代码显示的单元格中的信息？ [英] How to edit info in cells displayed via macro code in Excel?

问题描述

我有一个宏，所以当你在sheet1上突出显示一行时，宏会从这一行中获取所有信息，并在sheet2上显示。如果您在sheet1上突出显示不同的行，则sheet2上的信息是更改，以显示该行的信息。

我的问题是，如果我更改sheet2上显示的信息，它不会更改sheet1上的信息。有没有办法添加这个功能？

我现在有以下代码：

  Private Sub Worksheet_SelectionChange（ByVal Target As Range）
 Dim myList 
如果Target.Address<> Target.EntireRow.Address然后退出Sub 
如果Target.Rows.Count> 1然后退出Sub 
 myList = [{B1，B2，B3，B4，B5，B6，B7，B8，B9 ，B11，B12，B13，B14，B15}]'<  - 适应您的需要
 With Target.EntireRow 
对于i = 1 To UBound myList）
 Sheets（sheet2）。Range（myList（i））。Value = .Cells（i）.Value 
 Next 
 End With 
 End Sub 
  

任何帮助都会很棒！ ：）

解决方案

您正在使用 Worksheet_SelectionChange 事件宏来识别何时选择完整的单行。您需要为Sheet2 Worksheet_Change 事件宏，以识别B1：B15范围已更改，并将更改传递回Sheet1。

由于在更改值时触发了Worksheet_Change，因此您需要禁用 Application.EnableEvents属性，因此它不是从Sheet1的Worksheet_SelectionChange子文件中写入值触发。

您将需要一些公共变量。一个要记住更改应该返回的位置，另一个用于在Sheet2上定位目标单元格。这些只能在模块代码表中公开。

Book1 - Module1（Code）

  Option Explicit 
 
 Public Const sRNG As String =B1：B15
公共rRNG作为范围
  

我对您的原始Worksheet_SelectionChange进行了一些小的修改，并添加了禁用事件处理。

Book1 - Sheet1（Code）

  Option Explicit 
 
 Private Sub Worksheet_SelectionChange（ByVal Target As Range）
如果Target.Cells.Count = Columns.Count和Target.Rows.Count = 1和_ 
 CBool​​（Application.CountA（Target） ）然后'< ~~一个完整的非空行
错误GoTo bm_Safe_Exit 
 Application.EnableEvents = False 
 With Sheet2.Range（sRNG）
设置rRNG =目标。单元格（1，1）.Resize（.Columns.Count，.Rows.Count）
 .Cells = Application.Transpose（rRNG.Value ）
 End with 
 End If 
 
 bm_Safe_Exit：
 Application.EnableEvents = True 
 End Sub 
  / pre> 
 
 
工作表 .CodeName属性用于识别 Sheet2 ，因为如果工作表通常重命名，则不会更改。
 
 
 
它是有一点不清楚，你如何计划识别一行将值重新更改。我使用了Module1中声明的公共范围类型的变量来记录值从Sheet1传输到Sheet2的最后一个位置。 Sheet2的更改将返回到最后记录的位置。
 
 
 
  Book1  -  Sheet2（代码） 
  Option Explicit 
 
 Private Sub Worksheet_Change（ByVal Target As Range）
如果不相交（目标，范围（sRNG））不是然后
 Debug.Print rRNG.Address（0，0，external：= True）
错误GoTo bm_Safe_Exit 
 Application.EnableEvents = False 
 rRNG = Application.Transpose（Range sRNG）.Value）
 End If 
 
 bm_Safe_Exit：
 Application.EnableEvents = True 
 End Sub 
  
请注意，记住的位置仅在内存中。关闭和重新打开工作簿有效零。不要在Sheet2上进行更改，除非您从Sheet1新增了值。
 
I have a macro so that when you highlight a row on sheet1, the macro takes all the info from this row and displays this by itself on sheet2. If you highlight a different row on sheet1, the info on sheet2 is changes to show the info from that row.

My problem is that if I change the info displayed on sheet2, it doesn't change the info on sheet1. Is there a way I could add this functionality?

I have the following code at the moment:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim myList
If Target.Address <> Target.EntireRow.Address Then Exit Sub
If Target.Rows.Count > 1 Then Exit Sub
myList = [{"B1","B2","B3","B4","B5","B6","B7","B8","B9","B10","B11","B12","B13","B14","B15"}] '<- adjust to your need
With Target.EntireRow
    For i = 1 To UBound(myList)
        Sheets("sheet2").Range(myList(i)).Value = .Cells(i).Value
    Next
End With
End Sub
Any Help would be awesome! :)
解决方案
You are currently using a Worksheet_SelectionChange event macro to recognize when a full single row has been selected. You need a Worksheet_Change event macro for Sheet2 to recognize when values in the B1:B15 range have been changed and pass the changes back to Sheet1.

Because the Worksheet_Change is triggered on a change in values, you will need to disable the Application.EnableEvents property so that it is not triggered when you write the values from Sheet1's Worksheet_SelectionChange sub.

You are going to require a couple of public variables. One to remember the position that changes should be returned to and another to locate the target cells on Sheet2. These can only be made public in a module code sheet.

Book1 - Module1 (Code)
Option Explicit

Public Const sRNG As String = "B1:B15"
Public rRNG As Range
I've made a couple of small modifications to your original Worksheet_SelectionChange and added the disabling of event handling.

Book1 - Sheet1 (Code)
Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Cells.Count = Columns.Count And Target.Rows.Count = 1 And _
      CBool(Application.CountA(Target)) Then   '<~~ one complete non-blank row 
        On Error GoTo bm_Safe_Exit
        Application.EnableEvents = False
        With Sheet2.Range(sRNG)
            Set rRNG = Target.Cells(1, 1).Resize(.Columns.Count, .Rows.Count)
            .Cells = Application.Transpose(rRNG.Value)
        End With
    End If

bm_Safe_Exit:
    Application.EnableEvents = True
End Sub
The Worksheet .CodeName property was used to identify Sheet2 since this does not change if the worksheet is conventionally renamed.

It is a little unclear on how you were planning to identify the row to return the values to once they were changed. I've used a public range-type variable declared in Module1 to record the last location that values were transferred from Sheet1 to Sheet2. Changes on Sheet2 will return them to the last recorded location.

Book1 - Sheet2 (Code)
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range(sRNG)) Is Nothing Then
        Debug.Print rRNG.Address(0, 0, external:=True)
        On Error GoTo bm_Safe_Exit
        Application.EnableEvents = False
        rRNG = Application.Transpose(Range(sRNG).Value)
    End If

bm_Safe_Exit:
    Application.EnableEvents = True
End Sub
Note that the 'remembered' location is in memory only. Closing and reopening the workbook effectively 'zeroes' it. Do not make changes on Sheet2 unless you have freshly loaded values from Sheet1.

                        
这篇关于如何编辑通过Excel中的宏代码显示的单元格中的信息？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！