pop up box with conditions based on cell value. is that possible?

[fmoaveni]

Hello again,
I seem to be hitting another wall in my excel project. I have a box I have created in Excel 2013 when I click on the command button to input information on a specific sheet. The code reads as follows:
First code is for the OK button.
Second code is for the input boxes to go to the directed cells.


Private Sub OKButton_Click()
Sheet3.Activate
emptyRow = WorksheetFunction.CountA(Range("B:B")) + 1
Cells(emptyRow, 2).Value = PaletteCodeTextBox.Value
Cells(emptyRow, 3).Value = ArtikelNummerTextBox.Value
Cells(emptyRow, 6).Value = MengeTextBox.Value
Cells(emptyRow, 7).Value = PreisTextBox.Value
Cells(emptyRow, 8).Value = TischoderKartonTextBox.Value
Cells(emptyRow, 9).Value = FoododerNonfoodTextBox.Value
Cells(emptyRow, 10).Value = MHDTextBox.Value

End Sub

Private Sub UserForm_Click()
PaletteCodeTextBox.Value = ""
ArtikelNummerTextBox.Value = ""
MengeTextBox.Value = ""
PreisTextBox.Value = ""
TischoderKartonTextBox.Value = ""
FoododerNonfoodTextBox.Value = ""
MHDTextBox.Value = ""
PaletteCodeTextBox.SetFocus


End Sub


It currently puts the information that I input into the box, in the first available (empty) row on the "Eingeben" which is "sheet3" (renamed)

What I would like it to do, is to identify the next available line and input the information in the same cells I have listed (ie. columns 2,3,6,7,8,9,10) in the row it finds.

On "sheet1" I have 3 cells that show the values of the next available cells in column D which has a number that follows this series for example (330001, 330002, 330003...so on and so forth). Cells are C24, C25, C26 on "sheet1".

I do not know what to do to create a button that will read what this box says and automatically find the corresponding number in column D on the "Eingeben" sheet and then after I input the information into the popup box and click ok....fill in the lines for columns (2,3,6,7,8,9,10 on the same row).

I understand this may be confusing, but I am basically trying to create a system to know where things are stored. The numbers in column D are always going to stay constant and in order. The goal is to have something that will delete the old information and quickly show me which spots are available to store things (column D has these numbers) so that I can efficiently make the storage transactions and not lose track of things. I have basically everything working except for these command buttons.


Please let me know if you have questions and thank you in advance for any help on this.

 

[mumps]

I think that it would be much easier to follow if I could see how your data is organized. Perhaps you could upload a copy of your file to a free site such as www.box.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells and worksheets.

 

[fmoaveni]

https://app.box.com/s/yn2f6z9i5yaig1h0093a5hqljjcej96h

That at is the link to the file on my box.com account. Thank you for the suggestion. I hope that will help clear things up. I can't wait to hear back! Thank you again!

 

[mumps]

I'm not sure if understood correctly, but try this macro for the 'OK' button:

Private Sub OKButton_Click()
    Application.ScreenUpdating = False
    Dim foundNum As Range
    Set foundNum = Sheets("Eingeben").Range("D:D").Find(Range("G24"), LookIn:=xlValues, lookat:=xlWhole)
    If Not foundNum Is Nothing Then
        Sheets("Eingeben").Cells(foundNum.Row, 2).Value = PaletteCodeTextBox.Value
        Sheets("Eingeben").Cells(foundNum.Row, 3).Value = ArtikelNummerTextBox.Value
        Sheets("Eingeben").Cells(foundNum.Row, 6).Value = MengeTextBox.Value
        Sheets("Eingeben").Cells(foundNum.Row, 7).Value = PreisTextBox.Value
        Sheets("Eingeben").Cells(foundNum.Row, 8).Value = TischoderKartonTextBox.Value
        Sheets("Eingeben").Cells(foundNum.Row, 9).Value = FoododerNonfoodTextBox.Value
        Sheets("Eingeben").Cells(foundNum.Row, 10).Value = MHDTextBox.Value
    Else
        MsgBox ("Number not found.")
    End If
    Application.ScreenUpdating = True
    Unload Me
End Sub

If this works for you, your really don't need 3 numbers in range G24:G26 because the macro works with number a ta time.

 

[fmoaveni]

mumps! That worked so well! Thank you again! You must be an excel master

 

[mumps]

You are very welcome.