Macro to move code to cell, print, then move down the list to the next code

**MilesDeep** · 05-09-2014, 02:10 PM

We have wireless access codes in a column. If someone requires use of our guest wireless, I'd like for someone to access the spreadsheet, click on a button the prints out the access code.

I can get the code copied to the cell which is then printed. What I can't do is get the next code in the column to be copied over and printed should the button be clicked again. I seem to get the code in the first cell in the column to keep printing or I can get it to jump to the next cell but not the 3rd.

Also, can you turn off and on "Use Relative References" within a macro. It seems by my results it is either on or off.

Visual of Form:

code1
code2

Your wireless code is:

Click this button to get code

The button is clicked, 'Your wireless code is: code1' is printed.

The button is clicked again, 'Your wireless code is: code2' is printed.

And so on...

If I get the proper code it appear like this on the form

Your wireless code is: code1

code2

On the second click I want the code2 to copy to where code1 is.

**daffodil11** · 05-09-2014, 02:58 PM

Maybe:

Sub codegenerator()

On Error GoTo Default

    If IsNumeric(WorksheetFunction.Match(Range("D2"), Sheets("Sheet2").Range("A1:A10"), 0)) Then _

        Range("D2") = Sheets("Sheet2").Range("A" & WorksheetFunction.Match(Range("D2"), Sheets("Sheet2").Range("A1:A10"), 0) + 1)
    
        Else:
    
Default:
        Range("D2") = Sheets("Sheet2").Range("A1")

End If

End Sub

I had my code generated in D2.

My list of code was on Sheet2, A1:A10. If D2 is blank, it plops Sheet2A1. If it already contains one from the list, it instead gives the next one on the list.

**MilesDeep** · 05-09-2014, 04:59 PM

Excellent. I adjusted it to my needs and it works perfectly. It moves the next code into a "form" that is promptly printed. All at the click of a button. Great, thanks.