Excel VBA-根据单元格地址获取表的名称 [英] Excel VBA - Get name of table based on cell address
问题描述
我正在使用Excel,并且希望根据单元格地址(例如A3)获取表的名称,因此该单元格将不会移动.我将如何在Excel的VBA中说明这一点?
I am using Excel and am looking to get the name of the table based on a cell address (ex A3), this cell will not move. How would I go about stating this in Excel's VBA?
我的计划是要有代码将数据验证从维护"选项卡上的一行表复制到工作簿的每个选项卡上的单个表中(减去"TOC"和数据"选项卡).每个选项卡都是模板"工作表的副本(减去目录",数据"和模板(维护)"工作表).工作表的数据",模板"和模板(维护)"可能会隐藏,也可能不会隐藏.
My plan is to have code that will copy data validations from a row of one table on my Maintenance tab to a single table on each tab of my workbook (minus my "TOC" and "data" tabs). Each tab is a copy of a "TEMPLATE" worksheet (minus the "TOC", "data", & the "TEMPLATE (Maint.)" worksheets). Worksheets "data", "TEMPLATE", and "TEMPLATE (Maint.)" may or may not be hidden.
我在"Copy_Data_Validations"子目录中的代码如下:
The code I have in my "Copy_Data_Validations" sub is as follows:
Dim TotalSheets As Integer
Dim p As Integer
Dim iAnswer As VbMsgBoxResult
With Application
.DisplayAlerts = False
.ScreenUpdating = False
End With
'
' Move sheet "TOC" to the begining of the workbook.
'
Sheets("TOC").Move Before:=Sheets(1)
'
' Move sheet "data" to be the second sheet in the workbook.
'
Sheets("data").Move Before:=Sheets(2)
iAnswer = MsgBox("You are about to copy data validations!", vbOKCancel + vbExclamation _
+ vbDefaultButton2 + vbMsgBoxSetForeground, "Copying Data Valadations")
For TotalSheets = 1 To Sheets.Count
For p = 3 To Sheets.Count - 2
'
' If the answer is Yes, then copy data validations from "TEMPLATE (Maint.) to all other.
' sheets minus the "TOC" sheet and the "data" sheet.
'
If iAnswer = vbYes Then
If UCase$(Sheets(p).Name) <> "TOC" And UCase$(Sheets(p).Name) <> "data" Then
' This chunk of code should copy only the data validations
' of "Table1_1" (A4:AO4) from the maintenance tab to all
' rows of a single table on each worksheet (minus the
' "TOC", "data", & the "TEMPLATE (Maint.)" worksheets.
' This is the section of code I am looking for unless
' someone has something better they can come up with.
Selection.PasteSpecial Paste:=xlPasteValidation, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End If
'
' If the answer is Cancel, then cancels.
'
ElseIf iAnswer = vbCancel Then
' Add an exit here.
End If
With Application
.DisplayAlerts = True
.ScreenUpdating = True
End With
推荐答案
如果该单元格不是表的一部分,则尝试获取任何单元格的ListObject
名称都会导致错误.
Attempting to get the name of a ListObject
for any cell will cause an error if that cell is not a part of a table.
Option Explicit
Function CellInTable(thisCell As Range) As String
Dim tableName As String
tableName = ""
On Error Resume Next
tableName = thisCell.ListObject.Name
CellInTable = tableName
End Function
这篇关于Excel VBA-根据单元格地址获取表的名称的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!