Excel VBA: if multiple criteria and actions

[sonny1986]

Hello!

i want to develop a dynamically changing tool storage database, in which we edit our tools inventory (new entries, scrapping old tools -delete from the list, but these tools are stored on fixed locations, these locations will be avaible for new entries). I'd like to create an userform with dropbox, including a list of avaible free locations on a shelf (these are random locations). The first criteria is, where wants my collegues to store a new tool (up-middle-down on a shelf) and excel to show the avaible free locations and the operator to select from here where te tool to be stored (1 tool can be stored on 1 or max 6 locations (consecutive)). Now, it's working if i fill the first textbox ( UserForm2.ts1.Value), but if i want to save on more than 1 but less then 6 position, my code is stop working (i think, because is no value in the next cells&textboxes...). How i can bypass this, or skip form the beginning the code to be broke? Thanks for answers!

i use the following code for saving from textboxes the data into database:

Private Sub savebt_Click()

If UserForm2.ts1.Value = "" Then
MsgBox "Select location!", vbExclamation, "SL No"
Exit Sub
End If
q1 = UserForm2.ts1.Value
Sheets("CLISEE").Select
Dim rowselect As Double
Dim msg As String
Dim ans As String
rowselect = UserForm2.ts1.Value
rowselect = rowselect
Rows(rowselect).Select
Cells(rowselect - 998, 3) = UserForm2.TextBox12.Value
Cells(rowselect - 998, 4) = UserForm2.TextBox13.Value
Cells(rowselect - 998, 7) = UserForm2.ComboBox1.Value
Cells(rowselect - 998, 8) = UserForm2.ComboBox11.Value
Cells(rowselect - 998, 9) = UserForm2.TextBox25.Value

If UserForm2.ts2.Value = True Then
Do
q2 = UserForm2.ts2.Value
Sheets("CLISEE").Select
rowselect = UserForm2.ts2.Value
rowselect = rowselect
Rows(rowselect).Select
Cells(rowselect - 998, 3) = UserForm2.TextBox12.Value
Cells(rowselect - 998, 4) = UserForm2.TextBox13.Value
Cells(rowselect - 998, 7) = UserForm2.ComboBox1.Value
Cells(rowselect - 998, 8) = UserForm2.ComboBox11.Value
Cells(rowselect - 998, 9) = UserForm2.TextBox25.Value


q3 = UserForm2.ts3.Value
Sheets("CLISEE").Select
rowselect = UserForm2.ts3.Value
rowselect = rowselect
Rows(rowselect).Select
Cells(rowselect - 998, 3) = UserForm2.TextBox12.Value
Cells(rowselect - 998, 4) = UserForm2.TextBox13.Value
Cells(rowselect - 998, 7) = UserForm2.ComboBox1.Value
Cells(rowselect - 998, 8) = UserForm2.ComboBox11.Value
Cells(rowselect - 998, 9) = UserForm2.TextBox25.Value

q4 = UserForm2.ts4.Value
Sheets("CLISEE").Select
rowselect = UserForm2.ts4.Value
rowselect = rowselect
Rows(rowselect).Select
Cells(rowselect - 998, 3) = UserForm2.TextBox12.Value
Cells(rowselect - 998, 4) = UserForm2.TextBox13.Value
Cells(rowselect - 998, 7) = UserForm2.ComboBox1.Value
Cells(rowselect - 998, 8) = UserForm2.ComboBox11.Value
Cells(rowselect - 998, 9) = UserForm2.TextBox25.Value

q5 = UserForm2.ts5.Value
Sheets("CLISEE").Select
rowselect = UserForm2.ts5.Value
rowselect = rowselect
Rows(rowselect).Select
Cells(rowselect - 998, 3) = UserForm2.TextBox12.Value
Cells(rowselect - 998, 4) = UserForm2.TextBox13.Value
Cells(rowselect - 998, 7) = UserForm2.ComboBox1.Value
Cells(rowselect - 998, 8) = UserForm2.ComboBox11.Value
Cells(rowselect - 998, 9) = UserForm2.TextBox25.Value

q6 = UserForm2.ts6.Value
Sheets("CLISEE").Select
rowselect = UserForm2.ts6.Value
rowselect = rowselect
Rows(rowselect).Select
Cells(rowselect - 998, 3) = UserForm2.TextBox12.Value
Cells(rowselect - 998, 4) = UserForm2.TextBox13.Value
Cells(rowselect - 998, 7) = UserForm2.ComboBox1.Value
Cells(rowselect - 998, 8) = UserForm2.ComboBox11.Value
Cells(rowselect - 998, 9) = UserForm2.TextBox25.Value
Loop
End If
Sheets("CLISEE").Select


ThisWorkbook.Sheets("clisee").Range("t2:z2").ClearContents


End Sub

 

[Zack Barresse]

Hi there, and welcome to the board!

I'm trying to get a sense of what you're trying to do. There are some obvious fixes to your code, but the overall goal is eluding me still. How exactly are you using 'ts2', 'ts3', etc.? If 2 has a value and 3 is not, do the remaining controls matter if they have values, since the items must be consecutive? Can it be any set of controls? For example, if all are void of values except 'ts4' and 'ts5', what should happen? It's unclear what types of values these controls would house and the logic you want from it.

 

[sonny1986]

Hi there, and welcome to the board!

I'm trying to get a sense of what you're trying to do. There are some obvious fixes to your code, but the overall goal is eluding me still. How exactly are you using 'ts2', 'ts3', etc.? If 2 has a value and 3 is not, do the remaining controls matter if they have values, since the items must be consecutive? Can it be any set of controls? For example, if all are void of values except 'ts4' and 'ts5', what should happen? It's unclear what types of values these controls would house and the logic you want from it.

Hello Zack, thank you for helping me!

first f all, sorry for my bad english i try to explain shortly the logic: i need to store on shelfs the received printing plates (tool stereo = ts). One ts can have 1 up to 6 separate pieces. the shelfs has 3 levels: up, middle, down, and the location numbering starts from 1000 to 3929 in ascending order. because these tools can be very heavy, i want to let the operators to select where they want to store the tools on shelf, for easy handling (up, middle, down -if very it is heavy). So first, in listbox1 they select the position on shelf, than in listbox 2 appears the available locations (because old, unused tools will be scrapped, but the location of these scrapped tools will be available for new entries). If a new stereo tool has 3 pcs, that means that i need to save in my database in 3 separate row which reflects the location numbers of shelfs: for example: i have empty locations on 1000 -1005 positions, i want to save the TS1XXXX, which has 3 pcs, on 1000, 1001, 1002 . So, ts1, ts2, ts3, etc.. = the selected locations where i want to save the TS regarding infos from textboxes (like supplier, customer, TS identify code, etc). These infos i need to be stored for each selected row after i click on save button. It was very difficult (for me) to save the selected values from listbox2 into separate cells (but for a better looking for my collegues i put these values in separate textboxes from where i want vba to extract the values. so, if selected ts1, ts2, ts3 save data on these selected rows (1000-1002), than stop. Next new enty TS2XXXX, if it has 2 pcs, will be stored on the remaining empty locations 1003-1004, the another TS3XXXX if has only 1 pc, on 1005. I hope that the procedure is clear now, i know that is it a little complex... You can find a printscreen attached about how the userform looks like. Thank you! Br, Zoltan.

 

[Zack Barresse]

How do you know what each ts will take for spaces? I see where you select a location (Sus/Up, Jos/Down, Mijloc/Middle), how is Listbox2 populated with available spaces, or does it always list all spaces? I'm not sure how your text boxes are populated ('TS/LOCATIE 1', 'TS/LOCATIE 2', etc.). Is there a chance you can save a sample file to dropbox and provide a link? Or perhaps explain what should happen exactly as a desired output.

 

[sonny1986]

You can find my file on: dropbox.com/scl/fi/jsv4fpym9jn1k0g8vp8ds/Copy-of-Tool-Status-2020-_v1.xlsm?dl=0&rlkey=rkb6yp8fw0w84yi14ldhy9dr2
In column "F" where you can find the text "SPL"= that means free space. After every scrap, we delete the contents of the entire row (except location, and location number) and put this text in the specified column. this is how listbox 2 is populated, based on this text only freee, avaible locations appears in the box. I just want to solve the error when not all text boxes contains values (i think in IF formula can be added new criterias, but i don't know how to edit...). from listbox2 the values are saved in a single cell (t2), then with text to columns these values are saved in u2-z2 cells. based on the cell values the text boxes (TS/Locatie 1, etc.) are populated.

 

[Zack Barresse]

After they choose a Loc, they choose one or more Locatie, then what do they do? It looks like this is where you need logic, if I'm correct, to ensure the Locatie's chosen are contiguous and can fit the size needed - this is where some logic and definition is needed.

 

[sonny1986]

first, they fill with TS regarding informations all the textboxes, checkboxes, etc. (supplier, customer, ts number, wp name...), then, they select the needed location(s). With all these filled and selected, the form will be printed out, then they hit the save button, to save all these datas into database. So, if they select, for ex.: 1000, 1001 (TS1-TS2) - in these rows the same datas will be saved. My vba do not let me save the datas, because, in my opinion, it's missing a stop code if the rest of the text boxes (TS3, TS4,..) are empty.

 

[Zack Barresse]

Your textboxes, i.e. ts1, ts2, etc., they only get populated when the user clicks the 'Refresh' button? I'm trying to follow your code. If the user selects 1000 and 1001, then hits refresh, it appears your ListBox1 has all the data from CLISEE loaded to it. Should something else be happening?

If I select two items from 'locati' and click 'Refresh', it loads those values into ts1 and ts2. I can then go back and select a single item from 'locati' and hit 'Refresh' and it only updates ts1, leaving the others untouched. Is this behavior desired?

My assumption of what you want to happen is, when the user clicks 'Save', something should happen to those specified rows in CLISEE sheet, although I'm not sure what information is going where. Can you clarify this?

 

[sonny1986]

you're right, i forgot to mention that to send the selected values from listbox2 to cells and then to text boxes, first you need to click on "refresh" button. i will try to explain you in a step by step printscreen shots what i want to happen when my collegues clicks on save button. I will add at the end of the save button commands, a command to clear content from culomn "F" -> "SPL" - for next entry these location not to be shown.
dropbox.com/scl/fi/a5s7hlzslakisro15xoe4/Presentation1.pptx?dl=0&rlkey=xrr59kfan4fqct9pvqstqvahu