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,

Rick's rules of VBA include:

1. Never use Select
2. Never use Selection
3. Never use Activate
4. Never use Copy and Paste
5. If you see large block of text in the code then there may well be a better way.
6. Rules are meant to be broken!

Following the above I turned your code into this:

Sub TakePayment_Click()

    Dim wsDP As Worksheet
    Dim wsCI As Worksheet
    Set wsDP = ThisWorkbook.Worksheets("Door Purchase")
    Set wsCI = ThisWorkbook.Worksheets("Customer Info")
    
    With wsCI
        .Range("F6") = wsDP.Range("E10")
        .Range("G6") = wsDP.Range("I10")
        .Range("B6") = wsDP.Range("I15")
        .Range("E6") = wsDP.Range("E18")
        .Range("H6") = wsDP.Range("E23")
        .Range("I6") = wsDP.Range("E26")
    End With
    
    wsDP.Visible = False
    
End Sub

If you Dim a variable Excel will show you the IntelliSense dropdowns which I find useful.
Secondly, you can often shorten things and make them more readable.
Testing is sometimes easier because you can change worksheet by changing only one statement.
I am a bit pedantic about including ThisWorkbook in the definitions but if you sometimes work with more than one spreadsheet open it is sometimes a lifesaver. It is quite heart stopping when your test macro suddenly starts writing all over the master production spreadsheet!

The With construct effectively lets Excel keep its finger on the open page rather than closing the book, putting it back on the shelf, then having to open it again. It can save a bit of time.

My favourite piece of code to find the last row in column A is this one:

    With wsCI
        lr= .Cells(.Rows.Count, "A").End(xlUp).Row
    End With

Occasionally you need one which looks at all columns then I use one based on the Find command.

Combining that with what we have above and re-writing the Ranges() as Cells() to make stepping from one to another easier we get:

Sub TakePayment_Click()

    Dim wsDP As Worksheet
    Dim wsCI As Worksheet
    Dim NextRow As Long
    
    Set wsDP = ThisWorkbook.Worksheets("Door Purchase")
    Set wsCI = ThisWorkbook.Worksheets("Customer Info")
    
    With wsCI
        NextRow = .Cells(.Rows.Count, "F").End(xlUp).Row + 1
        .Cells(NextRow, "F") = wsDP.Range("E10")
        .Cells(NextRow, "G") = wsDP.Range("I10")
        .Cells(NextRow, "B") = wsDP.Range("I15")
        .Cells(NextRow, "E") = wsDP.Range("E18")
        .Cells(NextRow, "H") = wsDP.Range("E23")
        .Cells(NextRow, "I") = wsDP.Range("E26")
    End With
    
    wsDP.Visible = False
    
End Sub

 

[markbee]

Hi Rick,

WOW! - That's awesome! Thank you very much for that. Seems to be working a treat with some testing I've done. This Little Project is flying along now!

Ideally, I would like to be about to Print a receipt for the 'Door Paying Customers' via an additional Worksheet laid out in a receipt type format (approx. 58mm) I will try and utilise what you have taught me above in pulling data over to an additional sheet. I've seen this type of thing done with a EPOS Excel script and got it to print to my printer well. Would like to do a similar thing with this. Any advice you can give on achieving this and what to look out for?

Also, Can I ask if there is a way of Clearing Cell's when Finishing the Macro?

I've attempted this by to no avail

Sub TakePayment_Click()

    Dim wsDP As Worksheet
    Dim wsCI As Worksheet
    Dim NextRow As Long
    
    Set wsDP = ThisWorkbook.Worksheets("Door Purchase")
    Set wsCI = ThisWorkbook.Worksheets("Customer Info")
    
    With wsCI
        NextRow = .Cells(.Rows.Count, "F").End(xlUp).Row + 1
        .Cells(NextRow, "F") = wsDP.Range("E10")
        .Cells(NextRow, "G") = wsDP.Range("I10")
        .Cells(NextRow, "B") = wsDP.Range("I15")
        .Cells(NextRow, "E") = wsDP.Range("E18")
        .Cells(NextRow, "H") = wsDP.Range("E23")
        .Cells(NextRow, "I") = wsDP.Range("E26")


            wsDP.Range("E10").ClearContents
            wsDP.Range("I10").ClearContents
            wsDP.Range("I15").ClearContents
            wsDP.Range("E18").ClearContents
            wsDP.Range("E23").ClearContents
            wsDP.Range("E26").ClearContents


    End With
    
    wsDP.Visible = False

 

[RickXL]

Hi,

I might do a bit of tidying up but other than that I think the ClearContents approach is as good as any. I expect there are much cleverer ways but you need something that works and in 6 months time you will be clearly be able to see what it does.

Proposed tidy up:

Sub TakePayment_Click()

    Dim wsDP As Worksheet
    Dim wsCI As Worksheet
    Dim NextRow As Long
    
    Set wsDP = ThisWorkbook.Worksheets("Door Purchase")
    Set wsCI = ThisWorkbook.Worksheets("Customer Info")
    
    With wsCI
        NextRow = .Cells(.Rows.Count, "F").End(xlUp).Row + 1
        .Cells(NextRow, "F") = wsDP.Range("E10")
        .Cells(NextRow, "G") = wsDP.Range("I10")
        .Cells(NextRow, "B") = wsDP.Range("I15")
        .Cells(NextRow, "E") = wsDP.Range("E18")
        .Cells(NextRow, "H") = wsDP.Range("E23")
        .Cells(NextRow, "I") = wsDP.Range("E26")
    End With

    With wsDP
        .Range("E10").ClearContents
        .Range("I10").ClearContents
        .Range("I15").ClearContents
        .Range("E18").ClearContents
        .Range("E23").ClearContents
        .Range("E26").ClearContents
        .Visible = False
    End With

End Sub

That will keep all the wsDP changes in one block and all the wsCI ones in another.

Sorry, I don't do much printing but if you get stuck you know where we are

Regards,

 

[markbee]

Hi Rick,

Ah awesome! Many thanks for that.

I've used this and it's working a treat. However, Is there a way you can clear Merged Cells as well? This Syntax will clear Single Cells but not merged? any idea

Many thanks.

 

[RickXL]

Hi,

Rule #7:
Never use merged cells!

There are so many quirks about merged cells you are best avoiding them, in my humble opinion.

What you can do is set the contents of the top left hand cell to vbNullString or a pair of quotes ("").

For instance I merged G8:I10 and this will "clear" it.

Sheet1.Range("G8").Value = vbNullString

Note, the Sheet1.Range construct is just to provide a working example in one line. Still use the Dim/Set/With construct - or as much of it as makes sense.

 

[markbee]

Hi Rick,

Many thanks for your help on this.

I had written the following code with your guidance using DIMS

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").Cells(iRow, "M").Value = "CHECKED IN"
    Else
        Debug.Print iRow; " - Data invalid display an Error Message"
    End If
    
    Dim wsBI As Worksheet
    Set wsBI = ThisWorkbook.Worksheets("Booking In Form")
    
    With wsBI
    .Range("E4").Value = vbNullString
    .Range("E10").Value = vbNullString
    .Range("I10").Value = vbNullString
    .Range("E15").Value = vbNullString
    .Range("I15").Value = vbNullString
    .Range("E18").Value = vbNullString
    .Range("I18").Value = vbNullString
    .Range("E23").Value = vbNullString
    .Range("I23").Value = vbNullString
    .Range("E26").Value = vbNullString
    .Range("I26").Value = vbNullString
    
    End With
End Sub

The code works great, However the vbNullString also removes the Formula which is in there to bring up the ticket information once scanned in. Is there any way of adding to the above code to make it not clear the formula?

Many thanks for you help again so far. This is rocking along nicely.

 

[RickXL]

Sorry I am not understanding.

If the cell has a formula in it and you do not want to clear formulas then why are you clearing it?

What have I missed?

 

[markbee]

Hi RickXL,

Ah Yes, I can see your point there. I did not explain myself clearly enough

I would like the Cells to revert back to the #NA Value which shows before the Cell (which contains the barcode) is populated.

The only reason really is to try and help by not confusing the end-user who will be booking the attendee's in via the barcode scanning

P.S. Whilst on the subject. I would like to introduce some VB POP-UP Boxes that appear after the Booking in Button is pressed and also the Purchase Ticket option. Is this possible?

Many thanks as always.

 

[markbee]

Hi RickXL,

Actually.. Thinking about it. Instead of clearing the Cell's we could just re-populate them with the Formula which was in there before the Barcode was selected in the top cell?

Would that work?