When "CH" is entered in a cell in column 'M', I'd like a text box to appear
with "Really?" and a yes no option.
If No is selected, to clear the cell in 'M' but to remain with focus.
If Yes is selected, to run code called Sub Charge( ) which is located in
Module 1.

Any ideas?

Thanks

--
Traa Dy Liooar

Jock

not tested!
'=========================
sub Jock()

dim myRange as range

set myRange = worksheet("Sheet1").range("m1") 'change to suit

if myrange.value = "CH" then
me.textbox1.visible=true
me.optionyes.visible=true
me.optionno.visible=true
else
'do nothing
end if

end sub
'==============================
private sub optionyes_click()
call Charge
end sub
'=============================
private sub optionno_click()
dim myRange as range

set myRange = worksheet("Sheet1").range("m1") 'change to suit
myrange.value = ""

end sub
'===============================

right click your appropriate sheet tab & choose "View Code". Place
this code there. i used a textbox and option buttons from the ActiveX
Control Toolbox, not the Forms toolbar. if you want sub Jock to
happen automatically, you'd have to make it a worksheet_change sub.
hope it gets you started!
:)
susan


On Jan 6, 8:04*am, Jock wrote:
When "CH" is entered in a cell in column 'M', I'd like a text box to appear
with "Really?" and a yes no option.
If No is selected, to clear the cell in 'M' but to remain with focus.
If Yes is selected, to run code called Sub Charge( ) which is located in
Module 1.

Any ideas?

Thanks

--
Traa Dy Liooar

Jock

a few adjustments:

'=========================
sub Jock()

dim myRange as range

set myRange = worksheet("Sheet1").range("m1") 'change to suit

if myrange.value = "CH" then
me.textbox1.visible=true
me.textbox1.value = "Really?"
me.optionyes.visible=true
me.optionno.visible=true
else
'do nothing
end if

end sub
'==============================
private sub optionyes_click()
call Charge
end sub
'=============================
private sub optionno_click()
dim myRange as range

set myRange = worksheet("Sheet1").range("m1") 'change to suit
myrange.value = ""
myrange.select
me.textbox1.visible=false
me.optionyes.visible=false
me.optionno.visible=false

end sub
'===============================
susan




On Jan 6, 8:27*am, Susan wrote:
not tested!
'=========================
sub Jock()

dim myRange as range

set myRange = worksheet("Sheet1").range("m1") *'change to suit

if myrange.value = "CH" then
* *me.textbox1.visible=true
* *me.optionyes.visible=true
* *me.optionno.visible=true
else
* *'do nothing
end if

end sub
'==============================
private sub optionyes_click()
call Charge
end sub
'=============================
private sub optionno_click()
dim myRange as range

set myRange = worksheet("Sheet1").range("m1") *'change to suit
myrange.value = ""

end sub
'===============================

right click your appropriate sheet tab & choose "View Code". *Place
this code there. *i used a textbox and option buttons from the ActiveX
Control Toolbox, not the Forms toolbar. *if you want sub Jock to
happen automatically, you'd have to make it a worksheet_change sub.
hope it gets you started!
:)
susan

On Jan 6, 8:04*am, Jock wrote:



When "CH" is entered in a cell in column 'M', I'd like a text box to appear
with "Really?" and a yes no option.
If No is selected, to clear the cell in 'M' but to remain with focus.
If Yes is selected, to run code called Sub Charge( ) which is located in
Module 1.

Any ideas?

Thanks

--
Traa Dy Liooar

Jock- Hide quoted text -

- Show quoted text -

You can use the worksheet's Change event procedure to do this. Open
the Sheet's code module (right-click the worksheet tab and choose View
Code) and paste in the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Res As VbMsgBoxResult
Const TEST_RANGE = "M1:M100" '<<< CHANGE TO CELLS TO TEST
If Application.Intersect(Target, Me.Range(TEST_RANGE)) Is Nothing
Then
Exit Sub
End If
Application.EnableEvents = False
If StrComp(Target.Text, "ch", vbTextCompare) = 0 Then
' user entered 'ch'
Res = MsgBox("Really?")
If Res = vbNo Then
Target.Value = vbNullString
Else
Call Charge
End If
Else
' user didn't enter 'ch'
End If
Application.EnableEvents = True
End Sub


Change the value of TEST_RANGE to the range of cells you wish to test.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

On Tue, 6 Jan 2009 05:04:01 -0800, Jock
wrote:

When "CH" is entered in a cell in column 'M', I'd like a text box to appear
with "Really?" and a yes no option.
If No is selected, to clear the cell in 'M' but to remain with focus.
If Yes is selected, to run code called Sub Charge( ) which is located in
Module 1.

Any ideas?

Thanks

Thanks Chip, worked a treat!
--
Traa Dy Liooar

Jock


"Chip Pearson" wrote:

You can use the worksheet's Change event procedure to do this. Open
the Sheet's code module (right-click the worksheet tab and choose View
Code) and paste in the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Res As VbMsgBoxResult
Const TEST_RANGE = "M1:M100" '<<< CHANGE TO CELLS TO TEST
If Application.Intersect(Target, Me.Range(TEST_RANGE)) Is Nothing
Then
Exit Sub
End If
Application.EnableEvents = False
If StrComp(Target.Text, "ch", vbTextCompare) = 0 Then
' user entered 'ch'
Res = MsgBox("Really?")
If Res = vbNo Then
Target.Value = vbNullString
Else
Call Charge
End If
Else
' user didn't enter 'ch'
End If
Application.EnableEvents = True
End Sub


Change the value of TEST_RANGE to the range of cells you wish to test.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

On Tue, 6 Jan 2009 05:04:01 -0800, Jock
wrote:

When "CH" is entered in a cell in column 'M', I'd like a text box to appear
with "Really?" and a yes no option.
If No is selected, to clear the cell in 'M' but to remain with focus.
If Yes is selected, to run code called Sub Charge( ) which is located in
Module 1.

Any ideas?

Thanks

One possible issue I have noticed is that if the user types in "ch" rather
than picking it from a drop down list in the cell (didn't mention that
earlier) and hits enter, the focus moves one cell away. My code 'Charge( )
looks to see if the active cell has "ch" and if it does, copies the data from
the same row to another sheet. How can I ensure the focus remains on the cell
which has "ch" in it when <enter was hit?
--
Traa Dy Liooar

Jock


"Chip Pearson" wrote:

You can use the worksheet's Change event procedure to do this. Open
the Sheet's code module (right-click the worksheet tab and choose View
Code) and paste in the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Res As VbMsgBoxResult
Const TEST_RANGE = "M1:M100" '<<< CHANGE TO CELLS TO TEST
If Application.Intersect(Target, Me.Range(TEST_RANGE)) Is Nothing
Then
Exit Sub
End If
Application.EnableEvents = False
If StrComp(Target.Text, "ch", vbTextCompare) = 0 Then
' user entered 'ch'
Res = MsgBox("Really?")
If Res = vbNo Then
Target.Value = vbNullString
Else
Call Charge
End If
Else
' user didn't enter 'ch'
End If
Application.EnableEvents = True
End Sub


Change the value of TEST_RANGE to the range of cells you wish to test.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

On Tue, 6 Jan 2009 05:04:01 -0800, Jock
wrote:

When "CH" is entered in a cell in column 'M', I'd like a text box to appear
with "Really?" and a yes no option.
If No is selected, to clear the cell in 'M' but to remain with focus.
If Yes is selected, to run code called Sub Charge( ) which is located in
Module 1.

Any ideas?

Thanks