excel - 从用户窗体数据填充单元格。根据 `sub`，目标单元格会发生变化
问题描述
可以说我有samenstelling1和。samenstelling2samenstelling3
在每个子模块中，我调用我的用户表单samenstelling，用户填写 5 个字段。
如何获取用户表单上填写的数据以填充每个模块不同的某些单元格？
当用户OK在填写数据后按下时，我可以在 sub 中显示一个 msgbox，所以我知道 sub 与 userform 链接。我被困在如何将这些数据粘贴到某些单元格上。
对于samenstelling1单元格是“Q500:O500”，对于samenstelling2单元格是“Q501:O501”等。
代码为btnok
Public Sub btnok_Click()
Dim letter As String
Dim tekeningnr As String
Dim omschrijving As String
Dim posnummer As String
Dim revletter As String
tekeningnr = txttekeningnummer.Value
omschrijving = txtomschrijving.Value
revletter = cmbrevisieletter.Value
posnummer = cmbposnummer.Value
letter = UCase(cmbletter.Value)
Unload Me
End Sub
代码为samenstelling1
Sub samenstelling1()
Sheets("Artikelen_aanmaken").Activate
Dim letter As String
Dim tekeningnr As String
Dim omschrijving As String
Dim posnummer As String
Dim revletter As String
Samenstelling.UserForm_Initialize
Samenstelling.Show
'this part is not working because it doesn't receive that data from the userform, the cells stay empty
Range("q500") = cmbletter.Value
Range("N500") = txttekeningnummer.Value
Range("P500") = cmbrevisieletter.Value
Range("R500") = txtomschrijving.Value
Range("O500") = cmbposnummer.Value
Select Case posnummer
Case Is = 1
我也用下面的代码尝试过，但我认为它只是将我放入这些单元格中的数据定义为stringthen
Range("q500") = letter
Range("N500") = tekeningnr
Range("P500") = revletter.Value
Range("R500") = omschrijving.Value
Range("O500") = posnummer.Value
我所填写的 posnummers 数量的选择案例样本（所以这个选择案例在每个samenstelling_子..
Select Case posnummer
Case Is = 1
Range("18:30").EntireRow.Hidden = True
Range("A2:k2").Select
Range("A2:k2").AutoFill Destination:=Range("A2:k2"), Type:=xlFillSeries
Case Is = 2
Sheets("Artikelen_aanmaken").Activate
Range("18:30").EntireRow.Hidden = True
Range("A2:k2").Select
Range("A2:k2").AutoFill Destination:=Range("A2:k3"), Type:=xlFillSeries
Sheets("Artikelen_in_stuklijsten").Activate
Range("18:30").EntireRow.Hidden = True
Range("A2:c2").Select
Range("A2:c2").AutoFill Destination:=Range("A2:c3"), Type:=xlFillSeries
Range("d2:i2").Select
Range("d2:i2").AutoFill Destination:=Range("d2:i3"), Type:=xlFillDefault
Case Is = 3
Range("18:30").EntireRow.Hidden = True
Range("A2:k2").Select
Range("A2:k2").AutoFill Destination:=Range("A2:k4"), Type:=xlFillSeries
Sheets("Artikelen_in_stuklijsten").Activate
Range("18:30").EntireRow.Hidden = True
Range("A2:c2").Select
Range("A2:c2").AutoFill Destination:=Range("A2:c4"), Type:=xlFillSeries
Range("d2:i2").Select
Range("d2:i2").AutoFill Destination:=Range("d2:i4"), Type:=xlFillDefault
Case Is = 4
Range("18:30").EntireRow.Hidden = True
Range("A2:k2").Select
Range("A2:k2").AutoFill Destination:=Range("A2:k5"), Type:=xlFillSeries
Sheets("Artikelen_in_stuklijsten").Activate
Range("18:30").EntireRow.Hidden = True
Range("A2:c2").Select
Range("A2:c2").AutoFill Destination:=Range("A2:c5"), Type:=xlFillSeries
Range("d2:i2").Select
Range("d2:i2").AutoFill Destination:=Range("d2:i5"), Type:=xlFillDefault
Case Is = 5
Range("18:30").EntireRow.Hidden = True
Range("A2:k2").Select
Range("A2:k2").AutoFill Destination:=Range("A2:k6"), Type:=xlFillSeries
Sheets("Artikelen_in_stuklijsten").Activate
Range("18:30").EntireRow.Hidden = True
Range("A2:c2").Select
Range("A2:c2").AutoFill Destination:=Range("A2:c6"), Type:=xlFillSeries
Range("d2:i2").Select
Range("d2:i2").AutoFill Destination:=Range("d2:i6"), Type:=xlFillDefault
Case Is = 6
Range("18:30").EntireRow.Hidden = True
Range("A2:k2").Select
Range("A2:k2").AutoFill Destination:=Range("A2:k7"), Type:=xlFillSeries
Sheets("Artikelen_in_stuklijsten").Activate
Range("18:30").EntireRow.Hidden = True
Range("A2:c2").Select
Range("A2:c2").AutoFill Destination:=Range("A2:c7"), Type:=xlFillSeries
Range("d2:i2").Select
Range("d2:i2").AutoFill Destination:=Range("d2:i7"), Type:=xlFillDefault
Case Is = 7
Range("18:30").EntireRow.Hidden = True
Range("A2:k2").Select
Range("A2:k2").AutoFill Destination:=Range("A2:k8"), Type:=xlFillSeries
Sheets("Artikelen_in_stuklijsten").Activate
Range("18:30").EntireRow.Hidden = True
Range("A2:c2").Select
Range("A2:c2").AutoFill Destination:=Range("A2:c8"), Type:=xlFillSeries
Range("d2:i2").Select
Range("d2:i2").AutoFill Destination:=Range("d2:i8"), Type:=xlFillDefault
Case Is = 8
Range("18:30").EntireRow.Hidden = True
Range("A2:k2").Select
Range("A2:k2").AutoFill Destination:=Range("A2:k9"), Type:=xlFillSeries
Sheets("Artikelen_in_stuklijsten").Activate
Range("18:30").EntireRow.Hidden = True
Range("A2:c2").Select
Range("A2:c2").AutoFill Destination:=Range("A2:c9"), Type:=xlFillSeries
Range("d2:i2").Select
Range("d2:i2").AutoFill Destination:=Range("d2:i9"), Type:=xlFillDefault
Case Is = 9
Range("18:30").EntireRow.Hidden = True
Range("A2:k2").Select
Range("A2:k2").AutoFill Destination:=Range("A2:k10"), Type:=xlFillSeries
Sheets("Artikelen_in_stuklijsten").Activate
Range("18:30").EntireRow.Hidden = True
Range("A2:c2").Select
Range("A2:c2").AutoFill Destination:=Range("A2:c10"), Type:=xlFillSeries
Range("d2:i2").Select
Range("d2:i2").AutoFill Destination:=Range("d2:i10"), Type:=xlFillDefault
Case Is = 10
Range("18:30").EntireRow.Hidden = True
Range("A2:k2").Select
Range("A2:k2").AutoFill Destination:=Range("A2:k11"), Type:=xlFillSeries
Sheets("Artikelen_in_stuklijsten").Activate
Range("18:30").EntireRow.Hidden = True
Range("A2:c2").Select
Range("A2:c2").AutoFill Destination:=Range("A2:c11"), Type:=xlFillSeries
Range("d2:i2").Select
Range("d2:i2").AutoFill Destination:=Range("d2:i11"), Type:=xlFillDefault
Case Is = 11
Range("18:30").EntireRow.Hidden = True
Range("A2:k2").Select
Range("A2:k2").AutoFill Destination:=Range("A2:k12"), Type:=xlFillSeries
Sheets("Artikelen_in_stuklijsten").Activate
Range("18:30").EntireRow.Hidden = True
Range("A2:c2").Select
Range("A2:c2").AutoFill Destination:=Range("A2:c13"), Type:=xlFillSeries
Range("d2:i2").Select
Range("d2:i2").AutoFill Destination:=Range("d2:i13"), Type:=xlFillDefault
Case Is = 12
Range("18:30").EntireRow.Hidden = True
Range("A2:k2").Select
Range("A2:k2").AutoFill Destination:=Range("A2:k13"), Type:=xlFillSeries
Sheets("Artikelen_in_stuklijsten").Activate
Range("18:30").EntireRow.Hidden = True
Range("A2:c2").Select
Range("A2:c2").AutoFill Destination:=Range("A2:c14"), Type:=xlFillSeries
Range("d2:i2").Select
Range("d2:i2").AutoFill Destination:=Range("d2:i14"), Type:=xlFillDefault
Case Is = 13
Range("18:30").EntireRow.Hidden = True
Range("A2:k2").Select
Range("A2:k2").AutoFill Destination:=Range("A2:k14"), Type:=xlFillSeries
Sheets("Artikelen_in_stuklijsten").Activate
Range("18:30").EntireRow.Hidden = True
Range("A2:c2").Select
Range("A2:c2").AutoFill Destination:=Range("A2:c15"), Type:=xlFillSeries
Range("d2:i2").Select
Range("d2:i2").AutoFill Destination:=Range("d2:i15"), Type:=xlFillDefault
Case Is = 14
Range("18:30").EntireRow.Hidden = True
Range("A2:k2").Select
Range("A2:k2").AutoFill Destination:=Range("A2:k15"), Type:=xlFillSeries
Sheets("Artikelen_in_stuklijsten").Activate
Range("18:30").EntireRow.Hidden = True
Range("A2:c2").Select
Range("A2:c2").AutoFill Destination:=Range("A2:c16"), Type:=xlFillSeries
Range("d2:i2").Select
Range("d2:i2").AutoFill Destination:=Range("d2:i17"), Type:=xlFillDefault
Case Is = 15
Range("18:30").EntireRow.Hidden = True
Range("A2:k2").Select
Range("A2:k2").AutoFill Destination:=Range("A2:k16"), Type:=xlFillSeries
Sheets("Artikelen_in_stuklijsten").Activate
Range("18:30").EntireRow.Hidden = True
Range("A2:c2").Select
Range("A2:c2").AutoFill Destination:=Range("A2:c18"), Type:=xlFillSeries
Range("d2:i2").Select
Range("d2:i2").AutoFill Destination:=Range("d2:i18"), Type:=xlFillDefault
Case Is = 16
Range("18:30").EntireRow.Hidden = True
Range("A2:k2").Select
Range("A2:k2").AutoFill Destination:=Range("A2:k17"), Type:=xlFillSeries
Sheets("Artikelen_in_stuklijsten").Activate
Range("18:30").EntireRow.Hidden = True
Range("A2:c2").Select
Range("A2:c2").AutoFill Destination:=Range("A2:c19"), Type:=xlFillSeries
Range("d2:i2").Select
Range("d2:i2").AutoFill Destination:=Range("d2:i19"), Type:=xlFillDefault
Case Is = 17
Range("18:30").EntireRow.Hidden = True
Range("A2:k2").Select
Range("A2:k2").AutoFill Destination:=Range("A2:k18"), Type:=xlFillSeries
Sheets("Artikelen_in_stuklijsten").Activate
Range("18:30").EntireRow.Hidden = True
Range("A2:c2").Select
Range("A2:c2").AutoFill Destination:=Range("A2:c20"), Type:=xlFillSeries
Range("d2:i2").Select
Range("d2:i2").AutoFill Destination:=Range("d2:i20"), Type:=xlFillDefault
Case Is = 18
Range("18:30").EntireRow.Hidden = True
Range("A2:k2").Select
Range("A2:k2").AutoFill Destination:=Range("A2:k19"), Type:=xlFillSeries
Sheets("Artikelen_in_stuklijsten").Activate
Range("18:30").EntireRow.Hidden = True
Range("A2:c2").Select
Range("A2:c2").AutoFill Destination:=Range("A2:c21"), Type:=xlFillSeries
Range("d2:i2").Select
Range("d2:i2").AutoFill Destination:=Range("d2:i21"), Type:=xlFillDefault
Case Is = 19
Range("18:30").EntireRow.Hidden = True
Range("A2:k2").Select
Range("A2:k2").AutoFill Destination:=Range("A2:k14"), Type:=xlFillSeries
Sheets("Artikelen_in_stuklijsten").Activate
Range("18:30").EntireRow.Hidden = True
Range("A2:c2").Select
Range("A2:c2").AutoFill Destination:=Range("A2:c22"), Type:=xlFillSeries
Range("d2:i2").Select
Range("d2:i2").AutoFill Destination:=Range("d2:i22"), Type:=xlFillDefault
Case Is = 20
Range("18:30").EntireRow.Hidden = True
Range("A2:k2").Select
Range("A2:k2").AutoFill Destination:=Range("A2:k15"), Type:=xlFillSeries
Sheets("Artikelen_in_stuklijsten").Activate
Range("18:30").EntireRow.Hidden = True
Range("A2:c2").Select
Range("A2:c2").AutoFill Destination:=Range("A2:c23"), Type:=xlFillSeries
Range("d2:i2").Select
Range("d2:i2").AutoFill Destination:=Range("d2:i23"), Type:=xlFillDefault
Case Is = 21
Range("18:30").EntireRow.Hidden = True
Range("A2:k2").Select
Range("A2:k2").AutoFill Destination:=Range("A2:k16"), Type:=xlFillSeries
Sheets("Artikelen_in_stuklijsten").Activate
Range("18:30").EntireRow.Hidden = True
Range("A2:c2").Select
Range("A2:c2").AutoFill Destination:=Range("A2:c24"), Type:=xlFillSeries
Range("d2:i2").Select
Range("d2:i2").AutoFill Destination:=Range("d2:i24"), Type:=xlFillDefault
Case Is = 22
Range("18:30").EntireRow.Hidden = True
Range("A2:k2").Select
Range("A2:k2").AutoFill Destination:=Range("A2:k17"), Type:=xlFillSeries
Sheets("Artikelen_in_stuklijsten").Activate
Range("18:30").EntireRow.Hidden = True
Range("A2:c2").Select
Range("A2:c2").AutoFill Destination:=Range("A2:c25"), Type:=xlFillSeries
Range("d2:i2").Select
Range("d2:i2").AutoFill Destination:=Range("d2:i25"), Type:=xlFillDefault
Case Is = 23
Range("19:30").EntireRow.Hidden = True
Range("A2:k2").Select
Range("A2:k2").AutoFill Destination:=Range("A2:k18"), Type:=xlFillSeries
Sheets("Artikelen_in_stuklijsten").Activate
Range("18:30").EntireRow.Hidden = True
Range("A2:c2").Select
Range("A2:c2").AutoFill Destination:=Range("A2:c26"), Type:=xlFillSeries
Range("d2:i2").Select
Range("d2:i2").AutoFill Destination:=Range("d2:i26"), Type:=xlFillDefault
Case Is = 24
Range("20:30").EntireRow.Hidden = True
Range("A2:k2").Select
Range("A2:k2").AutoFill Destination:=Range("A2:k19"), Type:=xlFillSeries
Sheets("Artikelen_in_stuklijsten").Activate
Range("18:30").EntireRow.Hidden = True
Range("A2:c2").Select
Range("A2:c2").AutoFill Destination:=Range("A2:c27"), Type:=xlFillSeries
Range("d2:i2").Select
Range("d2:i2").AutoFill Destination:=Range("d2:i27"), Type:=xlFillDefault
解决方案
这不是完整的代码，但它应该可以帮助您入门。
Option Explicit
Public Sub btnok_Click()
Dim posnummer As Integer
Public Sub btnok_Click()
Dim letter As String
Dim tekeningnr As String
Dim omschrijving As String
Dim posnummer As String
Dim revletter As String
tekeningnr = txttekeningnummer.Value
omschrijving = txtomschrijving.Value
revletter = cmbrevisieletter.Value
posnummer = cmbposnummer.Value
letter = UCase(cmbletter.Value)
posnummer = "" 'Put the code which determines which row it should go on here.
Samenstelling (posnummer)
Unload Me
End Sub
Function Samenstelling(posnummer As Integer)
'Sheets("Artikelen_aanmaken").Activate 'No need to activate sheets, use the following instead, and call it before your range
Dim blad As Worksheet: blad = Sheets("Artikelen_aanmaken")
blad.Range("Q" & 500 + posnummer) = cmbletter.Value 'the 500 + posnummer means it starts at row 500, plus a row higher for every position you define. Start at zero (or change this to 499 +)
Range("N" & 500 + posnummer) = txttekeningnummer.Value
Range("P" & 500 + posnummer) = cmbrevisieletter.Value
Range("R" & 500 + posnummer) = txtomschrijving.Value
Range("O" & 500 + posnummer) = cmbposnummer.Value
End Sub
推荐阅读
- c++ - unordered_map 不应该只有两个参数吗？
- c - c语言中使用fork()处理socket中多个客户端的逻辑问题
- timer - STM32F4上定时器和spi之间的中断优先级问题
- javascript - 在提交时将随机数插入隐藏字段
- javascript - 错误的 POST 请求和使用 Ajax 的 GET 没有返回值
- python - 如何将每个段落从 .docx 文件复制到 .csv 文件中的新行？
- json - 有什么好的方法可以在 Flutter 中向 JSON 发出 POST 请求吗？
- php - @OneToOne 和 @JoinColumn 产生类型的期望值...异常
- javascript - 在 ajax 请求期间加载 gif 以添加
- javascript - 是否可以在 amcharts 向下钻取树形图上转到上一级？