如何将数据添加到单元格 - LIST和数据验证，Excel 2010中的Visual Basic [英] How do ADD data to cells - LIST and Data Validation, Visual Basic in Excel 2010

问题描述

我有一个列表使开关ON / OFF，如果需要打开列表，如果没有则关闭。

我希望当前单元格（已经是字符串）添加逗号并添加列表中的文本（保留
主要文本，a逗号，以及列表中的选项）。

sheet1.cell（1,1）.value =" 192_16"     
'活跃单元格

sheet1.cell（2,1）.value =" 167_12"     
'活跃单元格

sheet2.cell（1,1）.value =" old"            数据验证中的
'列表

sheet2.cell（2,1）.value =" new"           数据验证中的
'列表

sheet2.cell（3,1）.value =" etc"             数据验证中的
'列表

我需要，例如，sheet1.cell（1,1）.value = " 192_16，new"
或者说，例如，sheet1.cell（2,1）.value =" 167_12，old"

< p style ="line-height：normal; margin-bottom：0pt"> ... etc ...

我该怎么做？

解决方案

对于这个要求，我认为你需要更改
Worksheet_Change 方法中的数据。 / p>

例如：

 Private Sub Worksheet_Change（ByVal Target As Range）
如果Target.Address = Range（" B1"）。Address Then 
 Application.EnableEvents = False 
 Target.Value = [旧值]& "，" &安培; Target.Value 
 Application.EnableEvents = True 
 End if 
 End Sub 

要将验证添加到单元格，请参阅此处代码如下：

 With Range（" B1"）。验证
。删除
 .Add类型：= xlValidateList，AlertStyle：= xlValidAlertWarning ，_ 
运算符：= xlEqual，Formula1：=" = Sheet2！A1：A3" 
 .IgnoreBlank = True 
 .InCellDropdown = True 
 .InputTitle ="" 
 .ErrorTitle ="" 
 .InputMessage ="" 
 .ErrorMessage ="" 
 .ShowInput = True 
 .ShowError = True 
 End with 

要在单元格中更改值时获取旧值，您可以使用 Worksheet_SelectionChange 方法。

 Public Sub Worksheet_SelectionChange（ByVal Target As Range）
如果Target.Address = Range（" B1" ;）。地址然后
 oval = Target.Value 
结束如果
结束子

最好的问候

Starain Chen

I have a list made ​​the switch ON / OFF, if needed to turn ON the list, if not then OFF.

I wish the current cell, which is already a string, add a comma and the addition of a text from the list (as way as to preserve the primary text, a comma, and an option from the list).

sheet1.cell(1,1).value="192_16"     'active cell

sheet1.cell(2,1).value="167_12"     'active cell

sheet2.cell(1,1).value="old"           'list in data validation

sheet2.cell(2,1).value="new"          'list in data validation

sheet2.cell(3,1).value="etc"            'list in data validation

I need to, for example, sheet1.cell(1,1).value="192_16, new"
or that, for example, sheet1.cell(2,1).value="167_12, old"

... etc ...

How do I do this?

解决方案

Hi,

For this requirement, in my opinion, I think you need change the data in the Worksheet_Change method.

For example:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("B1").Address Then
  Application.EnableEvents = False
  Target.Value =[the old value] & "," & Target.Value
  Application.EnableEvents = True
End If
End Sub

To add the validation to the cell, please refer to this code below:

With Range("B1").Validation
 .Delete
 .Add Type:=xlValidateList, AlertStyle:=xlValidAlertWarning, _
 Operator:=xlEqual, Formula1:="=Sheet2!A1:A3"
 .IgnoreBlank = True
 .InCellDropdown = True
 .InputTitle = ""
 .ErrorTitle = ""
 .InputMessage = ""
 .ErrorMessage = ""
 .ShowInput = True
 .ShowError = True
End With

To get the old value when changed the value in cell, you could use Worksheet_SelectionChange method.

Public Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = Range("B1").Address Then
  oval = Target.Value
End If
End Sub

Best Regards

Starain Chen

这篇关于如何将数据添加到单元格 - LIST和数据验证，Excel 2010中的Visual Basic的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！