I have a spreadsheet that pulls information from two other sheets. I am now going through and automating it so that another store can use it and I no longer have to set it up for them.
I have created two userforms, and on one the forms the user can input the store data for their district. The userform has input fields for District #, Store name etc. Then I have checkboxes next to store names, and if you check one then it is representing that that store is your location, so that that information is dumped to a different area then the rest of the district.
I am stuggling with the following, one, detecting if the checkbox is true, and if it is setting StoreName to that value, and if it is false moving down the list until it becomes true.
All in all there is room for a user to input 13 stores.
Any help would be appreciated!
Here is my code thus far, please bear with me as it is crude as i am just learning.
Thanks!
I have created two userforms, and on one the forms the user can input the store data for their district. The userform has input fields for District #, Store name etc. Then I have checkboxes next to store names, and if you check one then it is representing that that store is your location, so that that information is dumped to a different area then the rest of the district.
I am stuggling with the following, one, detecting if the checkbox is true, and if it is setting StoreName to that value, and if it is false moving down the list until it becomes true.
All in all there is room for a user to input 13 stores.
Any help would be appreciated!
Here is my code thus far, please bear with me as it is crude as i am just learning.
Code:
Private Sub CancelButton_Click()
Unload Me
End Sub
Private Sub SaveButton_Click()
ActiveSheet.Unprotect
Dim SCell As Range
Dim SColumn As Range
Dim DColumn As Range
Dim DCell As Range
Set SColumn = ActiveSheet.Range("B:B")
For Each C In SColumn
If C.Offset(25, 0).Value = "" Then
Set SCell = C.Offset(25, 0)
Exit For
End If
Next
SCell.Offset(0, 0).Value = Store1.Value
SCell.Offset(1, 0).Value = Store2.Value
SCell.Offset(2, 0).Value = Store3.Value
SCell.Offset(3, 0).Value = Store4.Value
SCell.Offset(4, 0).Value = Store5.Value
SCell.Offset(5, 0).Value = Store6.Value
SCell.Offset(6, 0).Value = Store7.Value
SCell.Offset(7, 0).Value = Store8.Value
SCell.Offset(8, 0).Value = Store9.Value
SCell.Offset(9, 0).Value = Store10.Value
SCell.Offset(10, 0).Value = Store11.Value
SCell.Offset(11, 0).Value = Store12.Value
SCell.Offset(12, 0).Value = Store13.Value
SCell.Offset(13, 0).Value = DistrictNumber.Value
'End Store Names
'Start Store Display Names
Set DColumn = ActiveSheet.Range("C:C")
For Each C In DColumn
If C.Offset(20, 0).Value = "" Then
Set DCell = C.Offset(20, 0)
Exit For
End If
Next
DCell.Offset(4, 0).Value = Display1.Value
DCell.Offset(5, 0).Value = Display2.Value
DCell.Offset(6, 0).Value = Display3.Value
DCell.Offset(7, 0).Value = Display4.Value
DCell.Offset(8, 0).Value = Display5.Value
DCell.Offset(9, 0).Value = Display6.Value
DCell.Offset(10, 0).Value = Display7.Value
DCell.Offset(11, 0).Value = Display8.Value
DCell.Offset(12, 0).Value = Display9.Value
DCell.Offset(13, 0).Value = Display10.Value
DCell.Offset(14, 0).Value = Display11.Value
DCell.Offset(15, 0).Value = Display12.Value
DCell.Offset(16, 0).Value = Display13.Value
If StoreChk1 = True Then
StoreName = Store1.Value
End If
If StoreChk2 = True Then
StoreName = Store2.Value
End If
If StoreChk3 = True Then
StoreName = Store3.Value
End If
If StoreChk4 = True Then
StoreName = Store4.Value
End If
If StoreChk5 = True Then
StoreName = Store5.Value
End If
If StoreChk6 = True Then
StoreName = Store6.Value
End If
If StoreChk7 = True Then
StoreName = Store7.Value
End If
If StoreChk8 = True Then
StoreName = Store8.Value
End If
If StoreChk9 = True Then
StoreName = Store9.Value
End If
If StoreChk10 = True Then
StoreName = Store10.Value
End If
If StoreChk11 = True Then
StoreName = Store11.Value
End If
If StoreChk12 = True Then
StoreName = Store12.Value
End If
If StoreChk13 = True Then
StoreName = Store13.Value
End If
DCell.Offset(0, 0) = StoreName.Value
Display1 = ""
Display2 = ""
Display3 = ""
Display4 = ""
Display5 = ""
Display6 = ""
Display7 = ""
Display8 = ""
Display9 = ""
Display10 = ""
Display11 = ""
Display12 = ""
Display13 = ""
Store1 = ""
Store2 = ""
Store3 = ""
Store4 = ""
Store5 = ""
Store6 = ""
Store7 = ""
Store8 = ""
Store9 = ""
Store10 = ""
Store11 = ""
Store12 = ""
Store13 = ""
DistrictNumber = ""
'End Store Display Name
ActiveSheet.Protect
End Sub
Thanks!