Selecting a cell via userForm event handler

[jerH]

I'm trying to have the event handler from a userform select a cell on a worksheet. The form's showmodal property is false. I tried using activecell and was getting errors...I speculted that maybe Excel didn't consider the worksheet active because the form was up. So then I tried storing an address and then going from there.

So here's the test event handler:

Private Sub btnLeft_Click()


    Dim selectedCell As Range
    Dim selectedAddress As String
    
    ThisWorkbook.Worksheets("Board").Activate
    
    selectedAddress = ThisWorkbook.Worksheets("Board").Range("AG1").Value     'this cell has "D5" in it
        
    selectedCell = ThisWorkbook.Worksheets("Board").Range(selectedAddress)     'this line throws an error 
                                                                               'object variabe or with block variable not set 
    MsgBox selectedCell.Address
    


End Sub

Where am I going wrong?

 

[Jerry Sullivan]

Hi jerH,

That specific error is due to not using the Set keyword to assign the Range to the variable.

Set selectedCell = ThisWorkbook.Worksheets("Board").Range(selectedAddress)

 

[jerH]

Bah! What a newbie mistake!

Having fixed that, I now encounter the dreaded application defined or object defined error

Private Sub btnLeft_Click()


    Dim selectedCell As Range
    Dim selectedAddress As String
    
    ThisWorkbook.Worksheets("Board").Activate
    
    selectedAddress = ThisWorkbook.Worksheets("Board").Range("AG1").Value     'read the current address from the hidden cell off the screen
    
    Set selectedCell = ThisWorkbook.Worksheets("Board").Range(selectedAddress).Offset(0, -1)   ' move one to the left
    
    ThisWorkbook.Worksheets("Board").Range("AG1").Value = selectedCell.Address       'write the new address to the hidden cell
    
    ThisWorkbook.Worksheets("Board").Range(selectedCell).Select      'select the cell one to the left
                                                                                              'this is the line that throws the error     


End Sub

Can I abuse your expertise again?

 

[Jerry Sullivan]

What is the interface that you are trying to accomplish with the UserForm?

For example, does the user select an item from a list then you want the UserForm to close and the User to be shown a specific selected cell based on their choice?

 

[jerH]

This is actually part of a semester project to build a "connect four" game. The user form has buttons for "move left", "move right", and "drop". The worksheet "Board" has a visual representation of the board, and the userform is supposed to allow you to move the selected cell left and right across the top until you're at the column where you want to drop your piece.

So the form will stay up as you move left and right...eventually there will be checks to makes sure you haven't moved past the edges...and when you hit drop the form will close, the piece will be placed on the board, the routines that check for a win will run, and then, assuming there's no win, the form will come back up for the other player.

 

[Jerry Sullivan]

We're glad to help with school projects or homework here, but careful not to do homework for people.

I'd suggest you try using ActiveCell with Offset. There's no need to store the address of the Active Cell in another cell- that just makes it more complicated.

 

[jerH]

Understood....and activecell was my first approach but it threw errors as well. I'll continue to try to work around it...I figured the difficult part of this would be efficiently identifying wins, not the interface.

 

[jerH]

Another simple mistake....it's just activecell on it's own, not thisworkbook.worksheets("Board").activecell ...thanks for your help!