Wait for a cell to be selected...

[jgfcoimbra]

Hi all,

I have a userform with a CMd button, when I click on this button, it is supposed to activate sheet "sheet1" and I would like my code to wait there until any other cell has been selected( By default there is always one cell selected), so I would like my code to stay there until another cell has been selected.

Any ideas on how to do this?

Many thanks,
JC

[Simon Lloyd]

When you initialise your userform capture the address of the activecell, if your code (which you haven't supplied) selects a cell then you can use a variable to assign that address and check the activecell against it:[VBA]Dim MyCell As String
'your code
Range("A1").select
MyCell = Activecell.Address
'your code to do something
'more code
'now the check
If Activecell.Address <> MyCell Then
'rest of your code to run
Else
'do something else
MsgBox "You haven't changed cells"
End If[/VBA]

[jgfcoimbra]

Hi again.

This is my code with your suggested code embedded already.

[VBA]
Private ub Cmd_Overview_Click()
Dim Sheet_Name As String
Dim MyCell As String

Sheet_Name = ActiveSheet.Name
Sheets("PB4A_2010").Activate
ThisWorkbook.Sheets("PB4A_2010").Range("A1").Select

MyCell = ActiveCell.Address
If ActiveCell.Address <> MyCell Then
If IsDate(ActiveCell.Value) Then
Tb_Test_Starting_Day.Text = CStr(ActiveCell.Value)
Else
MsgBox ("This cell has no date data, please select other cell")
End If
Else
'do something else
MsgBox "You haven't changed cells"
End If

Sheets(Sheet_Name).Activate
End Sub
[/VBA]
Problem I have now is that in the userform once I press the button Cmd_Overview I will jump into the sheet PB4A_2010 and I got the message "You haven't changed cells", I would like that when I press the button it will wait until I select another cell apart from A1 and only after that proceed with the rest of the checks.

Is this possible?

Many thanks for your help
Joao

[Simon Lloyd]

How about supplying a workbook or at least all your code?

P.S please use code tags by either highlighting all your code and then clicking the VBA button or click the button first then paste your code between the tags.

[jgfcoimbra]

Hi,

This is all my code:

[VBA]
Private Sub Test_New()
Frm_Test_Manager.Show
End Sub

Sub Macro1()
Test_New
End Sub

Private ub Cmd_Overview_Click()
Dim Sheet_Name As String
Dim MyCell As String

Sheet_Name = ActiveSheet.Name
Sheets("PB4A_2010").Activate
ThisWorkbook.Sheets("PB4A_2010").Range("A1").Select

MyCell = ActiveCell.Address
If ActiveCell.Address <> MyCell Then
If IsDate(ActiveCell.Value) Then
Tb_Test_Starting_Day.Text = CStr(ActiveCell.Value)
Else
MsgBox ("This cell has no date data, please select other cell")
End If
Else
'do something else
MsgBox "You haven't changed cells"
End If

Sheets(Sheet_Name).Activate
End Sub
[/VBA]

[Simon Lloyd]

Please, in future use code tags as i indictated above!

[jgfcoimbra]

Hi,

Sorry about that, I forgot to do that, it will not happen again ;-)