Hi, can anyone help with User Form VBA?
I have Data on Sheets 7, 8 & 9 that I want to copy into a grid on Sheet2 when the User Form meets a certain criteria
I have built the User Form with the toolbox functions as follows
CommandButton1
ComboBox1 with
Sheet7
Sheet8
Sheet9
ListBox1 with
0
1
2
3
4
5
CheckBox1 Labelled A
CheckBox2 Labelled B
CheckBox3 Labelled x5 (LabelBox1 'Rng' as a Header)
CheckBox4 Labelled x10
On Sheet2 the Grid Range is ("AM17:AS23") with Headers A,B,C,D,E,F,G ("AM16:AS16")
and has Data Manually added to ("AM17:AS17") As and when Required
On Sheet7 the Data Range to be copied from is ("BC17:BL28") and the same for Sheets 8 & 9
with Headers "Ref" "Rng" "Seq" A,B,C,D,E,F,G
eg
<tbody>
</tbody><strike></strike>
With the User Form I want to be able to Select different Data from Sheets 7, 8 & 9
eg if I Select 'Sheet7' from ComboBox1, '0' from ListBox1, CheckBox1 = 'True' CheckBox3 = 'True' and click the Command Button, Data from Sheet7 ("BF17:BL17") should be copied to the Grid on Sheet2 and Transposed to ("AM17:AM23")
Then if I Selected 'Sheet7' again from ComboBox1, '0' from ListBox1, CheckBox2 = 'True' CheckBox4 = 'True' and click
</strike><strike></strike><strike></strike><strike style="background-color: transparent; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; word-spacing: 0px;"></strike><strike></strike>
Any help would be appreciated
Regards
pwill
I have Data on Sheets 7, 8 & 9 that I want to copy into a grid on Sheet2 when the User Form meets a certain criteria
I have built the User Form with the toolbox functions as follows
CommandButton1
ComboBox1 with
Sheet7
Sheet8
Sheet9
ListBox1 with
0
1
2
3
4
5
CheckBox1 Labelled A
CheckBox2 Labelled B
CheckBox3 Labelled x5 (LabelBox1 'Rng' as a Header)
CheckBox4 Labelled x10
On Sheet2 the Grid Range is ("AM17:AS23") with Headers A,B,C,D,E,F,G ("AM16:AS16")
and has Data Manually added to ("AM17:AS17") As and when Required
On Sheet7 the Data Range to be copied from is ("BC17:BL28") and the same for Sheets 8 & 9
with Headers "Ref" "Rng" "Seq" A,B,C,D,E,F,G
eg
AJ | AK | AL | AM | AN | AO | AP | AQ | AR | AS | AT | AU | AV | BA | BB | BC | BD | BE | BF | BG | BH | BI | BJ | BK | BL | BM | BN | BO | |||
15 | Sheet2 | 15 | Sheet7 | |||||||||||||||||||||||||||
16 | A | B | C | D | E | F | G | 16 | Ref | Rng | Seq | A | B | C | D | E | F | G | ||||||||||||
17 | 0 | 0 | 1 | 2 | 2 | 4 | 5 | 17 | 0 | 5 | A | 0 | 0 | 0 | 0 | 1 | 1 | 1 | ||||||||||||
18 | 18 | 0 | 10 | B | 0 | 0 | 0 | 0 | 2 | 2 | 2 | |||||||||||||||||||
19 | 19 | 1 | 5 | A | 1 | 1 | 1 | 1 | 2 | 2 | 2 | |||||||||||||||||||
20 | 20 | 1 | 10 | B | 1 | 1 | 1 | 1 | 3 | 3 | 3 | |||||||||||||||||||
21 | 21 | 2 | 5 | A | 2 | 2 | 2 | 2 | 3 | 3 | 3 | |||||||||||||||||||
22 | 22 | 2 | 10 | B | 2 | 2 | 2 | 2 | 4 | 4 | 4 | |||||||||||||||||||
23 | 23 | 3 | 5 | A | 3 | 3 | 3 | 3 | 5 | 5 | 5 | |||||||||||||||||||
24 | 24 | 3 | 10 | B | 3 | 3 | 3 | 3 | 6 | 6 | 6 | |||||||||||||||||||
25 | 25 | 4 | 5 | A | 4 | 4 | 4 | 4 | 7 | 7 | 7 | |||||||||||||||||||
26 | 26 | 4 | 5 | B | 4 | 4 | 4 | 4 | 8 | 8 | 8 | |||||||||||||||||||
27 | 27 | 5 | 5 | A | 5 | 5 | 5 | 5 | 9 | 9 | 9 | |||||||||||||||||||
28 | 28 | 5 | 10 | A | 5 | 5 | 5 | 5 | 10 | 10 | 10 | |||||||||||||||||||
29 | 29 | |||||||||||||||||||||||||||||
30 | 30 |
<tbody>
</tbody>
With the User Form I want to be able to Select different Data from Sheets 7, 8 & 9
eg if I Select 'Sheet7' from ComboBox1, '0' from ListBox1, CheckBox1 = 'True' CheckBox3 = 'True' and click the Command Button, Data from Sheet7 ("BF17:BL17") should be copied to the Grid on Sheet2 and Transposed to ("AM17:AM23")
Then if I Selected 'Sheet7' again from ComboBox1, '0' from ListBox1, CheckBox2 = 'True' CheckBox4 = 'True' and click
the Command Button, Data from Sheet7 ("BF18:BL18") should be copied to the Grid on Sheet2 and Transposed to the next available column ("AN17:AN23") as the first column in the Grid ("AM17:AM23") would already have Data and so on...
Then continue filling the Grid as required
ie
</strike>'Sheet7' from ComboBox1, '2' from ListBox1, CheckBox2 = 'True' CheckBox4 = 'True'
<strike></strike><strike></strike><strike></strike>Then continue filling the Grid as required
ie
'Sheet7' from ComboBox1, '1' from ListBox1, CheckBox1 = 'True' CheckBox3 = 'True'
<strike></strike><strike>and click the Command Button
'Sheet7' from ComboBox1, '2' from ListBox1, CheckBox1 = 'True' CheckBox3 = 'True'
and click the Command Button
</strike>'Sheet7' from ComboBox1, '2' from ListBox1, CheckBox2 = 'True' CheckBox4 = 'True'
and click the Command Button
</strike>
'Sheet7' from ComboBox1, '4' from ListBox1, CheckBox2 = 'True' CheckBox3 = 'True'
<strike>and click the Command Button
</strike>
'Sheet7' from ComboBox1, '5' from ListBox1, CheckBox1 = 'True' CheckBox4 = 'True'
<strike>and click the Command Button
The Grid would have the following Data
<tbody>
</tbody>
The Grid would have the following Data
AJ | AK | AL | AM | AN | AO | AP | AQ | AR | AS | AT | AU | AV | |
15 | Sheet2 | ||||||||||||
16 | A | B | C | D | E | F | G | ||||||
17 | 0 | 0 | 1 | 2 | 2 | 4 | 5 | ||||||
18 | 0 | 0 | 1 | 2 | 2 | 4 | 5 | ||||||
19 | 0 | 0 | 1 | 2 | 2 | 4 | 5 | ||||||
20 | 0 | 0 | 1 | 2 | 2 | 4 | 5 | ||||||
21 | 1 | 2 | 2 | 3 | 4 | 8 | 10 | ||||||
22 | 1 | 2 | 2 | 3 | 4 | 8 | 10 | ||||||
23 | 1 | 2 | 2 | 3 | 4 | 8 | 10 | ||||||
24 | |||||||||||||
25 | |||||||||||||
26 | |||||||||||||
27 | |||||||||||||
28 | |||||||||||||
29 | |||||||||||||
30 |
<tbody>
</tbody>
</strike>
Any help would be appreciated
Regards
pwill
Last edited: