Calling a module from textbox

J

Jock

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
 
S

Susan

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
 
S

Susan

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
 
C

Chip Pearson

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)
 
J

Jock

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?
 
Top