quattroformatee
New Member
- Joined
- Apr 25, 2023
- Messages
- 2
- Office Version
- 365
- Platform
- Windows
Hello everyone,
I'm trying to automate specific tasks for our business and decided to rework our excel files. These excel files are used to upload specific products automatically. The base idea is to insert new rows with predefined values with checkboxes. I've already written some code which would fit our needs, but the problem is that it's pretty hard coded. So when we implement new products and colors I have to hard code new checkboxes as well and edit the column and row output for every product and color. This is the code I wrote:
I've tested it already and it's working like in screenshot (Tests). Now I'm looking for some easier way in writing this code so that it takes the data from our database, creates the text we need and inserts new rows with the specific formatting into the spreadsheet when the checkboxes are activated, so that it will look like in the screenshot (Endresult). We have multiple products with multiple colors and everytime I enable the checkbox for a specific product it should automatically create new rows with the values from the database. Does anyone have an idea with which code snippet I could realize that easily?
I'm trying to automate specific tasks for our business and decided to rework our excel files. These excel files are used to upload specific products automatically. The base idea is to insert new rows with predefined values with checkboxes. I've already written some code which would fit our needs, but the problem is that it's pretty hard coded. So when we implement new products and colors I have to hard code new checkboxes as well and edit the column and row output for every product and color. This is the code I wrote:
VBA Code:
Private Sub CheckBox3_Click()
ID = 1
n = ID + 1
Datenbank = "Tabelle1"
Output = "Eingabe"
'Variables Output Sheet
SKU = Sheets(Output).Range("E4").Value
Artikelname = Sheets(Output).Range("F4").Value
'Variables Database
Artikel = Sheets(Datenbank).Range("H" & n).Value
Artikelname_Zusatz = Sheets(Datenbank).Range("F" & n).Value
SKU_Zusatz = Sheets(Datenbank).Range("L" & n).Value
Typ = Sheets(Datenbank).Range("B" & n).Value
'If Start
If CheckBox3.Value = True And CheckBox4.Value = True Then
'Parent SKU Output
Sheets(Output).Range("L1").Value = SKU + "-" + Typ
Sheets(Output).Range("K2").Value = SKU + "-" + Typ
Sheets(Output).Range("K3").Value = SKU + "-" + Typ
Sheets(Output).Range("K4").Value = SKU + "-" + Typ
Sheets(Output).Range("K5").Value = SKU + "-" + Typ
Sheets(Output).Range("K6").Value = SKU + "-" + Typ
Sheets(Output).Range("K7").Value = SKU + "-" + Typ
'Child SKU Output
Sheets(Output).Range("L2").Value = SKU + "-" + SKU_Zusatz
Sheets(Output).Range("L3").Value = SKU + "-" + Sheets("Tabelle1").Range("L" & n + 1).Value
Sheets(Output).Range("L4").Value = SKU + "-" + Sheets("Tabelle1").Range("L" & n + 2).Value
Sheets(Output).Range("L5").Value = SKU + "-" + Sheets("Tabelle1").Range("L" & n + 3).Value
Sheets(Output).Range("L6").Value = SKU + "-" + Sheets("Tabelle1").Range("L" & n + 4).Value
Sheets(Output).Range("L7").Value = SKU + "-" + Sheets("Tabelle1").Range("L" & n + 5).Value
'Artikel Output
Sheets(Output).Range("M1").Value = Artikelname + " " + Artikel
'Artikelname Output
Sheets(Output).Range("M2").Value = Artikelname + " " + Artikelname_Zusatz
Sheets(Output).Range("M3").Value = Artikelname + " " + Sheets(Datenbank).Range("F" & n + 1).Value
Sheets(Output).Range("M4").Value = Artikelname + " " + Sheets(Datenbank).Range("F" & n + 2).Value
Sheets(Output).Range("M5").Value = Artikelname + " " + Sheets(Datenbank).Range("F" & n + 3).Value
Sheets(Output).Range("M6").Value = Artikelname + " " + Sheets(Datenbank).Range("F" & n + 4).Value
Sheets(Output).Range("M7").Value = Artikelname + " " + Sheets(Datenbank).Range("F" & n + 5).Value
Else
'Parent SKU Clear
Sheets(Output).Range("L1").Clear
Sheets(Output).Range("K2").Clear
Sheets(Output).Range("K3").Clear
Sheets(Output).Range("K4").Clear
Sheets(Output).Range("K5").Clear
Sheets(Output).Range("K6").Clear
Sheets(Output).Range("K7").Clear
'Child SKU Clear
Sheets(Output).Range("L2").Clear
Sheets(Output).Range("L3").Clear
Sheets(Output).Range("L4").Clear
Sheets(Output).Range("L5").Clear
Sheets(Output).Range("L6").Clear
Sheets(Output).Range("L7").Clear
'Artikel Clear
Sheets(Output).Range("M1").Clear
'Artikelname Clear
Sheets(Output).Range("M2").Clear
Sheets(Output).Range("M3").Clear
Sheets(Output).Range("M4").Clear
Sheets(Output).Range("M5").Clear
Sheets(Output).Range("M6").Clear
Sheets(Output).Range("M7").Clear
End If
End SubI've tested it already and it's working like in screenshot (Tests). Now I'm looking for some easier way in writing this code so that it takes the data from our database, creates the text we need and inserts new rows with the specific formatting into the spreadsheet when the checkboxes are activated, so that it will look like in the screenshot (Endresult). We have multiple products with multiple colors and everytime I enable the checkbox for a specific product it should automatically create new rows with the values from the database. Does anyone have an idea with which code snippet I could realize that easily?
