Selecting a cell via userForm event handler

jerH

Board Regular
Joined
Dec 3, 2008
Messages
168
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:

Code:
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?
 
Last edited:

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
Hi jerH,

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

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

jerH

Board Regular
Joined
Dec 3, 2008
Messages
168
Bah! What a newbie mistake!

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

Code:
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

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
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

Board Regular
Joined
Dec 3, 2008
Messages
168

ADVERTISEMENT

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.
 
Last edited:

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
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

Board Regular
Joined
Dec 3, 2008
Messages
168
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

Board Regular
Joined
Dec 3, 2008
Messages
168
Another simple mistake....it's just activecell on it's own, not thisworkbook.worksheets("Board").activecell ...thanks for your help!
 

Watch MrExcel Video

Forum statistics

Threads
1,133,538
Messages
5,659,384
Members
418,500
Latest member
Guru Prasad S

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top