excel - Excel Userform 编码修改
问题描述
我需要有关如何修改以下代码的帮助。当标题位于模板的第 1 行时,我能够创建模板以输入用户表单中所需的所有信息。但是当我需要将标题重新定位到第 29 行时。即使我修改了代码以匹配第 29 行,它也不能按预期工作。请帮助。
这是文件https://1drv.ms/x/s!AixhKuqjnB1cgW8qhYoRMmt0oN0o?e=W52afT
您将找到“原始”选项卡。原始 VBA 编码与第 1 行中的标题一起使用。“CID”选项卡将是我需要修改代码以使用移动到第 29 行的标题的选项卡。
这是与第 1 行中的标题一起使用的原始代码
Sub Refresh_Data()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Original")
Dim last_row As Long
last_row = Application.WorksheetFunction.CountA(sh.Range("A:A"))
With Me.ListBox1
.ColumnHeads = True
.ColumnCount = 12
.ColumnWidths = "30,100,100,70,100,100,50,100,50,50,120,200"
If last_row = 1 Then
.RowSource = "Original!A2:L2"
Else
.RowSource = "Original!A2:L" & last_row
End If
End With
End Sub
Private Sub Add_Click()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Original")
Dim last_row As Long
last_row = Application.WorksheetFunction.CountA(sh.Range("A:A"))
'Validations---------------------------------------------------------------------------------------
If Me.TextBox1.Value = "" Then
MsgBox "Please Fill Signal Name. If it is not required, fill -", vbCritical
Exit Sub
End If
'------------------
If Me.TextBox2.Value = "" Then
MsgBox "Please Fill (From) Connector REF DES", vbCritical
Exit Sub
End If
'------------------
If Me.TextBox3.Value = "" Then
MsgBox "Please Fill (From) Connector Pin Location", vbCritical
Exit Sub
End If
'------------------
If Me.TextBox4.Value = "" Then
MsgBox "Please Fill Contact P/N or Supplied with Connector", vbCritical
Exit Sub
End If
'------------------
If Me.TextBox5.Value = "" Then
MsgBox "Please Fill Wire Gauge", vbCritical
Exit Sub
End If
'------------------
If Me.TextBox6.Value = "" Then
MsgBox "Please Fill Wire/Cable P/N", vbCritical
Exit Sub
End If
'------------------
If Me.TextBox7.Value = "" Then
MsgBox "Please Fill (To) Connector REF DES", vbCritical
Exit Sub
End If
'------------------
If Me.TextBox8.Value = "" Then
MsgBox "Please Fill (To) Pin Location", vbCritical
Exit Sub
End If
'------------------
If Me.TextBox9.Value = "" Then
MsgBox "Please Fill Contact P/N or Supplied with Connector", vbCritical
Exit Sub
End If
'------------------
If Me.ComboBox10.Value = "" Then
MsgBox "Use Drop Down Arrow to Select Wire Color", vbCritical
Exit Sub
End If
'--------------------------------------------------------------------------------------------------
sh.Range("A" & last_row + 1).Value = "=Row()-1"
sh.Range("B" & last_row + 1).Value = Me.TextBox1.Value
sh.Range("C" & last_row + 1).Value = Me.TextBox2.Value
sh.Range("D" & last_row + 1).Value = Me.TextBox3.Value
sh.Range("E" & last_row + 1).Value = Me.TextBox4.Value
sh.Range("F" & last_row + 1).Value = Me.TextBox5.Value
sh.Range("G" & last_row + 1).Value = Me.TextBox6.Value
sh.Range("H" & last_row + 1).Value = Me.TextBox7.Value
sh.Range("I" & last_row + 1).Value = Me.TextBox8.Value
sh.Range("J" & last_row + 1).Value = Me.TextBox9.Value
sh.Range("K" & last_row + 1).Value = Me.ComboBox10.Value
sh.Range("L" & last_row + 1).Value = Me.TextBox11.Value
'------------------
Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Me.TextBox4.Value = ""
Me.TextBox5.Value = ""
Me.TextBox6.Value = ""
Me.TextBox7.Value = ""
Me.TextBox8.Value = ""
Me.TextBox9.Value = ""
Me.ComboBox10.Value = ""
Me.TextBox11.Value = ""
'------------------
Call Refresh_Data
End Sub""
解决方案
对标题行使用常量,以后很容易更改。
Option Explicit
Const HEADER = 29
Private Sub CommandButton1_Click()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("CID")
Dim last_row As Long
last_row = sh.Cells(Rows.Count, "A").End(xlUp).Row
If last_row < HEADER Then
last_row = HEADER
End If
Dim arMsg(10) As String, n As Integer, msg As String
arMsg(1) = "Signal Name. If it is not required, fill -"
arMsg(2) = "(From) Connector REF DES"
arMsg(3) = "(From) Connector Pin Location"
arMsg(4) = "Contact P/N or Supplied with Connector"
arMsg(5) = "Wire Gauge"
arMsg(6) = "Wire/Cable P/N"
arMsg(7) = "(To) Connector REF DES"
arMsg(8) = "(To) Pin Location"
arMsg(9) = "Contact P/N or Supplied with Connector"
arMsg(10) = "Use Drop Down Arrow to Select Wire Color"
For n = 1 To 9
If Me.Controls("TextBox" & n).Value = "" Then
msg = msg & vbLf & n & ") " & arMsg(n)
End If
Next
If Me.Controls("ComboBox10").Value = "" Then
msg = msg & vbLf & arMsg(10)
End If
If Len(msg) > 0 Then
MsgBox "Please Fill " & msg, vbCritical
Exit Sub
End If
Dim c As Control
With sh.Range("A" & last_row + 1)
.Offset(0, 0).Value = "=Row()-" & HEADER
For n = 1 To 11
If n = 10 Then
Set c = Me.Controls("ComboBox" & n)
Else
Set c = Me.Controls("TextBox" & n)
End If
.Offset(0, n).Value = c.Value
c.Value = ""
Next
End With
Call Refresh_Data(sh)
End Sub
Sub Refresh_Data(sh As Worksheet)
Dim last_row As Long
last_row = sh.Cells(Rows.Count, "A").End(xlUp).Row
With Me.ListBox1
.ColumnHeads = True
.ColumnCount = 12
.ColumnWidths = "30,100,100,70,100,100,50,100,50,50,120,200"
If last_row <= HEADER Then
last_row = HEADER + 1
End If
.RowSource = sh.Name & "!A" & HEADER + 1 & ":L" & last_row
End With
End Sub
推荐阅读
- iis - 在 IIS 中运行 slim 时找不到页面
- excel - Excel 仅引用我正在编写公式的工作表中的单元格
- python - 如何使用 SWIG 接口在 python 中访问 C++ typedef'd 结构
- java - 使用 setText() 具有固定字符串的动态计数器?
- java - Java 中 Arrays.deepEquals() 的时间复杂度
- django - 如何在基于 SimpleListFilter 的过滤器 django 中覆盖 empty_label?
- ios - 在 Metal 的计算函数中使用 `stage_in`
- ios - SpriteKit跳转后如何让玩家节点站立?
- python - 在 django 模型中添加和删除主键后如何修复 id 字段中的缺失值?
- c++ - 从 WinVerifyTrust 获取 TRUST_E_NO_SIGNER_CERT