Help creating a Marco that will Populate another Cell on a Separate sheet dependent on a data validation list

[markbee]

Hi guys,

My first post! Wh00!

Okay, I'm a little stuck around these VB Macro's. I've done some C# about a year ago (a bit rusty now) but I understand the concepts so hopefully any technical jargon and methods should be understood fine. I've been asked to put together a Quick (and dirty) little Checking-in system for ticket that are sold. Currently I've created the Database with test records and the front GUI.

I know this should be doable with a embedded Macro as I've managed to do it against 1 record but not multiple... When you scan the Barcode it will appear with the ticket info in the relevant boxes. Then you click the 'BOOK IN' button the Macro currently puts a Flag as "CheckedIn" (or something) in an additional column on the data table.

My issue is this will currently only work with one particular cell I hard coded into the Macro but would want it to move down the data table and Flag each ticket as it's scanned. It will need to iterate through the data depending on the Barcode ID Currently selected.

Hope this makes sense?! I've Linked some screenshots below.

https://www.dropbox.com/s/12ehboa4tmp0cz9/Screen1.PNG
https://www.dropbox.com/s/zps5ia04e0si460/Screen2.PNG?dl=0

Hoping you guys can point me in the right direct to get this nailed. That would be great!

Thank you in advance for any help given and I look forward to hearing your thoughts

 

[RickXL]

Hi and welcome to the MrExcel Message Board,

One way would be to use the worksheet Match function to locate the barcode number row.

For instance, the code behind the button could include something like this:

Private Sub CommandButton1_Click()
    Dim iRow As Variant
    iRow = Application.Match(TextBox1.Text, Range("A:A"), 0)
    If IsNumeric(iRow) Then
        Debug.Print iRow; " - Data Valid - process the Data"
    Else
        Debug.Print iRow; " - Data invalid display an Error Message"
    End If
End Sub

The variable iRow needs to be a variant because it will also be used for the error return code.
Columna will probably need to be formatted as text.
It might work more quickly if you can limit the rows in column A that are used. A:A means it will check all 1 million rows.

The Debug.Print statements would be replaced with your processing.

 

[markbee]

Hi RickXL,

Many thanks for your quick response. I quickly dumped the withing a Macro (As it's late here) to see what the result I would getting was and it was the following:

https://www.dropbox.com/s/bc2p186cdza5o5o/Screen3.PNG?dl=0

When Debugging it Hilights the row stated below:

iRow = Application.Match(TextBox1.Text, Range("A:A"), 0)

Should 'TextBox1.Text' be changed to a Cell? As the Screen shots I sent were not text boxes but Cell's made to look like Text Boxes. (Cells with Boarders)

Look forward to your reply.

 

[RickXL]

As the Screen shots I sent were not text boxes but Cell's made to look like Text Boxes. (Cells with Boarders)

You sure fooled me!

Yes, the first entry should point to the place where the id number is stored.
If that is a cell then quote the cell. For example, if the id is in cell Z9 you would need:

iRow = Application.Match(Range("Z9"), Range("A:A"), 0)

 

[markbee]

Hi RickXKL

Ah awesome! - Glad I was not going mad! ha-ha..

Which Syntax would I add to the 'Range(A:A)' to make it point and a Different worksheet (I.E. Where the table info is stored) Also, I'm assuming by using the 'Debug.Print' Syntax this would print to screen? However, I want it to Print a Certain value to a Cell?

Unless I'm reading that part wrong and if so, Apologies

Thanks for your help so far though!

I'm Assuming something like this?:

Sub BookedIn_Click()    Dim iRow As Variant
    iRow = Application.Match(Range("E4"), Sheets("Customer Info").Range("A:A"), 0)
    If IsNumeric(iRow) Then
        Debug.Print iRow; Sheets("Customer Info").[M2].Value = "CHECKED IN"
    Else
        Debug.Print iRow; " - Data invalid display an Error Message"
    End If
End Sub

 

[RickXL]

Hi,

I was just using the Debug.Print command as an example of some code. It is used for debugging and it prints messages out to the Immediate Window in the VB Editor. I had not intended you to leave those in.

Something like this should work:

Sub BookedIn_Click()    
    Dim iRow As Variant
    iRow = Application.Match(Range("E4"), Sheets("Customer Info").Range("A:A"), 0)
    If IsNumeric(iRow) Then
        Sheets("Customer Info").[M2].Value = "CHECKED IN"
    Else
        Debug.Print iRow; " - Data invalid display an Error Message"
    End If
End Sub

 

[markbee]

Hi RickXL,

Ah great! Thanks for that. The Code you sent works well as long as I want to print "Checked In" into the Cell M2 (and as long as the Code in E4 Matches on the same as a Barcode) on A:A which is does.

However, When selecting a Different Barcode in E4 the marco will still print the Value M2, even if the Barcode is sitting at Cell A6 (which means the Checked In should go into M6) - Does that make sense?

Is there a way to pass over the row number and which M Cell to paste the 'Checked In' text to?

Hi,

I was just using the Debug.Print command as an example of some code. It is used for debugging and it prints messages out to the Immediate Window in the VB Editor. I had not intended you to leave those in.

Something like this should work:

Sub BookedIn_Click()    
    Dim iRow As Variant
    iRow = Application.Match(Range("E4"), Sheets("Customer Info").Range("A:A"), 0)
    If IsNumeric(iRow) Then
        Sheets("Customer Info").[M2].Value = "CHECKED IN"
    Else
        Debug.Print iRow; " - Data invalid display an Error Message"
    End If
End Sub

 

[RickXL]

Hi,

Yes, try something like this:

ub BookedIn_Click()    
    Dim iRow As Variant
    iRow = Application.Match(Range("E4"), Sheets("Customer Info").Range("A:A"), 0)
    If IsNumeric(iRow) Then
        Sheets("Customer Info").Cells(iRow,"M").Value = "CHECKED IN"
    Else
        Debug.Print iRow; " - Data invalid display an Error Message"
    End If
End Sub

 

[markbee]

Good morning RickXL,

You sir.. are a good man! Thank you very much for this. Works a dream!

Now I understand the Syntax of what is happening I will try and work on this a little further as I want to be able to do the following:

- IF a Ticket is booked in already, Grey cells out and show POP-UP Message to end-user.
- Create a 'New Ticket Purchase' screen if we have door-sales
- Utilize the print function to print our a Till-style receipt from our Bluetooth receipt printer in the correct size (I'm sure that will be a joy! -_-)

But thank you very much for guiding me through this part!

Regards
Mark

 

[markbee]

Hi RickXL,

I've now moved onto the new part of this mini-project (Being able to create ticket sales on the Door).

I've cloned the screen onto a different page and utilised the following code so far to copy a On-the-Door ticket purchase to the 'Customer Info' sheet.

Sub Purchase_Click()Sheets("Door Purchase").Visible = True
Sheets("Door Purchase").Select
End Sub
Sub TakePayment_Click()
Sheets("Door Purchase").Range("E10").Copy Destination:=Sheets("Customer Info").Range("F6")
Sheets("Door Purchase").Range("I10").Copy Destination:=Sheets("Customer Info").Range("G6")
Sheets("Door Purchase").Range("I15").Copy Destination:=Sheets("Customer Info").Range("B6")
Sheets("Door Purchase").Range("E18").Copy Destination:=Sheets("Customer Info").Range("E6")
Sheets("Door Purchase").Range("E23").Copy Destination:=Sheets("Customer Info").Range("H6")
Sheets("Door Purchase").Range("E26").Copy Destination:=Sheets("Customer Info").Range("I6")
Sheets("Door Purchase").Range("E26").Copy Destination:=Sheets("Customer Info").Range("I6")
Sheets("Door Purchase").Visible = False
End Sub

This works but as per usual it needs modifying to achieve what I need it to do:
1. Needs to paste the relevant cell values to the next available Row which does not already have data within it. (Otherwise It will always post to the Same Cells, so I'm guessing we will need to iterate through the rows of data somehow?)
2. Need to be pasted into the "Customer Info" worksheet with all formats from the original cells on the "Door Purchase" worksheet stripped.
3. On Closure of the "Door Purchase" Worksheet I would like to clear the Cells, ready for use by the next ticket sale
4. A Pop-up message appears after clicking the 'Take Payment' button stating "Ticket purchased" (or something like that).
5. Populate the 'Checked In' section automatically IF a Row of Data comes in from the "Door Purchase" screen.

I have provided screenshots below:

https://www.dropbox.com/s/ykxw5dkujc73abi/BookedIn-SS1.PNG?dl=0
https://www.dropbox.com/s/ln5rhvkolx05cxw/BookedIn-SS2.PNG?dl=0
https://www.dropbox.com/s/nju13sw3qywg8v3/BookedIn-SS3.PNG?dl=0

Once again, I appreciate any help you are able to provide with this, It feels like i'm starting to learn a lot more VBA as this project progresses,

Many thanks.
Mark