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 <J...@discussions.microsoft.com> 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 <bogenex...@aol.com> 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 <J...@discussions.microsoft.com> 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
<(E-Mail Removed)> 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
> <(E-Mail Removed)> 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
> <(E-Mail Removed)> 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
>