Auto Enable Macro

  • Thread starter Thread starter atiq8961
  • Start date Start date
A

atiq8961

I download SpellIndian function by Yogi Anand from anandent.com, for my
invoice template, I insert code in to VBE standard module. Now I want to
automate the code i.e. when I open worksheet it is auto enable and when I
close worksheet it is automtic disable.
I have one worksheet for my invoice template. I don't understand how can I
automate my macro.
I am not a programmer, so please someone explaine me in details.
My code is below.
Function SpellIndian(ByVal MyNumber)

'**** Yogi Anand -- ANAND Enterprises -- Rochester Hills MI 48309 --
248-375-5710 www.anandent.com
'**** Last updated 03-Oct-2003
'**** SpellIndian (modified on 20-Sep-2003 to 1) show Rupees to
precede, and to show "" for 0 paise)
'**** ySpellRupees (on 20-Nov-2002)
'**** Excel UDF to spell Indian Currency -- Rupees and Paise into text
'**** Indian currency starts off with 1000s, and after that only with
100s
'**** 1000 (Thousand) -- 1,00,000 (Lac or Lakh) -- 1,00,00,000 (Crore)
-- 1,00,00,00,000 (Arab)
'**** (this UDF is based on SpellNumber by Microsoft)
'****************' Main Function *'****************

Dim Rupees, Paise, Temp
Dim DecimalPlace, Count
Redim Place(9) As String
Place(2) = " Thousand "
Place(3) = " Lac "
Place(4) = " Crore "
Place(5) = " Arab " ' String representation of amount
MyNumber = Trim(Str(MyNumber)) ' Position of decimal place 0 if none
DecimalPlace = InStr(MyNumber, ".")
' Convert Paise and set MyNumber to Rupee amount
If DecimalPlace > 0 Then
Paise = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2))
MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
End If
Count = 1
Do While MyNumber <> ""
If Count = 1 Then Temp = GetHundreds(Right(MyNumber, 3))
If Count > 1 Then Temp = GetHundreds(Right(MyNumber, 2))
If Temp <> "" Then Rupees = Temp & Place(Count) & Rupees
If Count = 1 And Len(MyNumber) > 3 Then
MyNumber = Left(MyNumber, Len(MyNumber) - 3)
Else
If Count > 1 And Len(MyNumber) > 2 Then
MyNumber = Left(MyNumber, Len(MyNumber) - 2)
Else
MyNumber = ""
End If
End If
Count = Count + 1
Loop
Select Case Rupees
Case ""
Rupees = "No Rupees"
Case "One"
Rupees = "One Rupee"
Case Else
'****************************************************************
'Yogi Anand on 20-Sep-2003
'modified the following two lines to display "Rupees" to precede
' rem'd the first line and added the second line
'****************************************************************
'Rupees = Rupees & " Rupees"
Rupees = "Rupees " & Rupees

End Select
Select Case Paise
Case ""
'****************************************************************
'Yogi Anand on 20-Sep-2003
'modified the following two lines to display nothing for no paise
' rem'd the first line and added the second line
'****************************************************************

'Paise = " and No Paise"
'****************************************************************
'Yogi Anand on 03-Oct-2003
'modified the following line to display " Only" for no paise
' rem'd the first line and added the second line
'****************************************************************
'Paise = ""
Paise = " Only"
Case "One"
Paise = " and One Paisa"
Case Else
Paise = " and " & Paise & " Paise"

End Select
SpellIndian = Rupees & Paise
End Function
'*******************************************
' Converts a number from 100-999 into text *
'*******************************************
Function GetHundreds(ByVal MyNumber)
Dim Result As String
If Val(MyNumber) = 0 Then Exit Function
MyNumber = Right("000" & MyNumber, 3) 'Convert the hundreds place
If Mid(MyNumber, 1, 1) <> "0" Then
Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
End If
'Convert the tens and ones place
If Mid(MyNumber, 2, 1) <> "0" Then
Result = Result & GetTens(Mid(MyNumber, 2))
Else
Result = Result & GetDigit(Mid(MyNumber, 3))
End If
GetHundreds = Result
End Function
'*********************************************
' Converts a number from 10 to 99 into text. *
'*********************************************
Function GetTens(TensText)
Dim Result As String
Result = "" ' null out the temporary function value
If Val(Left(TensText, 1)) = 1 Then ' If value between 10-19
Select Case Val(TensText)
Case 10: Result = "Ten"
Case 11: Result = "Eleven"
Case 12: Result = "Twelve"
Case 13: Result = "Thirteen"
Case 14: Result = "Fourteen"
Case 15: Result = "Fifteen"
Case 16: Result = "Sixteen"
Case 17: Result = "Seventeen"
Case 18: Result = "Eighteen"
Case 19: Result = "Nineteen"
Case Else
End Select
Else ' If value between 20-99
Select Case Val(Left(TensText, 1))
Case 2: Result = "Twenty "
Case 3: Result = "Thirty "
Case 4: Result = "Forty "
Case 5: Result = "Fifty "
Case 6: Result = "Sixty "
Case 7: Result = "Seventy "
Case 8: Result = "Eighty "
Case 9: Result = "Ninety "
Case Else
End Select
Result = Result & GetDigit _
(Right(TensText, 1)) 'Retrieve ones place
End If
GetTens = Result
End Function
'*******************************************
' Converts a number from 1 to 9 into text. *
'*******************************************
Function GetDigit(Digit)
Select Case Val(Digit)
Case 1: GetDigit = "One"
Case 2: GetDigit = "Two"
Case 3: GetDigit = "Three"
Case 4: GetDigit = "Four"
Case 5: GetDigit = "Five"
Case 6: GetDigit = "Six"
Case 7: GetDigit = "Seven"
Case 8: GetDigit = "Eight"
Case 9: GetDigit = "Nine"
Case Else: GetDigit = ""
End Select
End Function

"I am so stuppid in explain my post so please forget it".
 
Hi
You have four functions here

SpellIndian
GetHundreds
GetTens
GetDigit

In VBA you can have functions and subs. It is subs that "do" things
automatically, like write onto worksheets or create charts or switch
between worksheets. Functions are there to perform calculations.
Functions don't change what you see on the screen or "do" things
automatically. You cannot automate functions - they must be used by a
sub and you automate the sub.
e.g.
Sub Test()
Dim x as Double, y as String
x = worksheets(1).Cells(1, 1).Value
y = SpellIndian(x)
worksheets(1).Cells(1, 2).Value = y
end Sub

This sub will read the value in A1 and call it x. It then calculates
SpellIndian(x) and calls it y. It then puts the value y into cell A2.
SpellIndian itself cannot put a value in A2 - a function can't "do"
anything to the worksheet. You cannot put the line

worksheets(1).Cells(1, 2).Value = y

inside the SpellIndian function.

The functions ARE available to you as functions in a worksheet when
you open the workbook. In a worksheet cell type

= Spellindian(

and you will be prompted for a number, which can be a value in another
cell. e.g. Put 1 in A1 and in A2 type

= Spellindian( A1)

regards
Paul
 
Paul Robinson said:
Hi
You have four functions here

SpellIndian
GetHundreds
GetTens
GetDigit

In VBA you can have functions and subs. It is subs that "do" things
automatically, like write onto worksheets or create charts or switch
between worksheets. Functions are there to perform calculations.
Functions don't change what you see on the screen or "do" things
automatically. You cannot automate functions - they must be used by a
sub and you automate the sub.
e.g.
Sub Test()
Dim x as Double, y as String
x = worksheets(1).Cells(1, 1).Value
y = SpellIndian(x)
worksheets(1).Cells(1, 2).Value = y
end Sub

This sub will read the value in A1 and call it x. It then calculates
SpellIndian(x) and calls it y. It then puts the value y into cell A2.
SpellIndian itself cannot put a value in A2 - a function can't "do"
anything to the worksheet. You cannot put the line

worksheets(1).Cells(1, 2).Value = y

inside the SpellIndian function.

The functions ARE available to you as functions in a worksheet when
you open the workbook. In a worksheet cell type

= Spellindian(

and you will be prompted for a number, which can be a value in another
cell. e.g. Put 1 in A1 and in A2 type

= Spellindian( A1)

regards
Paul



.
Dear sir,
Click to expand...
with reference to my previous post,
many thanks for your reply.
Mybe my explaination mistake, I insert SpellIndian function in to "Cell A1"
and my amount in "CellB1". Result is o.k. Now I close my worksheet and after
some time when I reopen my worksheet dilog box appears and asked for macro
enable/disable. I want to automate this event when I open worksheet it is
auto enable and when I close worksheet it is automatically disable.
Please help with this issue.
Thnaks.
 
Hi
with reference to my previous post,
many thanks for your reply.
Mybe my explaination mistake, I insert SpellIndian function in to "Cell A1"
and my amount in "CellB1". Result is o.k. Now I close my worksheet and after
some time when I reopen my worksheet dilog box appears and asked for macro
enable/disable. I want to automate this event when I open worksheet it is
auto enable and when I close worksheet it is automatically disable.
Please help with this issue.
Thnaks.-
Click to expand...

Hi
That is simply a macro security issue.
In Excel 2003 go to Tools, Macro, Security..In advanced tab check on
"Trust Access to visual Basic Project". Save the file and close excel.
Open Excel again and you should not get the warning message.
In Excel 2007
1. click the round Office button then the button excel Options at the
bottom. Click on "Show developer tab in the ribbon"
2. Within Developer Tab click on Macro Security.
3. Within Macro Settings check on "Trust access to the VBA project
object model"
4. You could also check on "Trust all macros.." but it might be safer
to go to Trusted Locations and tell excel that your PC is trusted.

regards
Paul
 
Dear Paul sir,
I am using excel2003, as per your instructions, Tools>Macro>Security>In
advance tab, and I select "Trust Access to Visual Basic Project" and saved my
file and closed excel. When I reopen my file agian security warning appears
and asked for enable/disable macro.
My security level set on medium.
Thanks for co-operation.
Atiq
 
If just for your own computer, create a digital certificate using the Office
SelfCert tool.

Sign the workbook/project using that DC

See more on this in 2003 help on "digital certificates"


Gord Dibben MS Excel MVP
 
Dear Gord Dibben Sir / Paul Robinsons Sir,
Thanks for your help,
Have a nice and happy weekend.
Regards
Atiq
 
I download SpellIndian function by Yogi Anand from anandent.com, for my
invoice template, I insert code in to VBE standard module. Now I want to
automate the code i.e. when I open worksheet it is auto enable and when I
close worksheet it is automtic disable.
I have one worksheet for my invoice template. I don't understand how can I
automate my macro.
I am not a programmer, so please someone explaine me in details.
My code is below.
Function SpellIndian(ByVal MyNumber)

'**** Yogi Anand -- ANAND Enterprises -- Rochester Hills MI 48309 --
248-375-5710 www.anandent.com
'**** Last updated 03-Oct-2003
'**** SpellIndian (modified on 20-Sep-2003 to 1) show Rupees to
precede, and to show "" for 0 paise)
'**** ySpellRupees (on 20-Nov-2002)
'**** Excel UDF to spell Indian Currency -- Rupees and Paise into text
'**** Indian currency starts off with 1000s, and after that only with
100s
'**** 1000 (Thousand) -- 1,00,000 (Lac or Lakh) -- 1,00,00,000 (Crore)
-- 1,00,00,00,000 (Arab)
'**** (this UDF is based on SpellNumber by Microsoft)
'****************' Main Function *'****************

Dim Rupees, Paise, Temp
Dim DecimalPlace, Count
Redim Place(9) As String
Place(2) = " Thousand "
Place(3) = " Lac "
Place(4) = " Crore "
Place(5) = " Arab " ' String representation of amount
MyNumber = Trim(Str(MyNumber)) ' Position of decimal place 0 if none
DecimalPlace = InStr(MyNumber, ".")
' Convert Paise and set MyNumber to Rupee amount
If DecimalPlace > 0 Then
Paise = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2))
MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
End If
Count = 1
Do While MyNumber <> ""
If Count = 1 Then Temp = GetHundreds(Right(MyNumber, 3))
If Count > 1 Then Temp = GetHundreds(Right(MyNumber, 2))
If Temp <> "" Then Rupees = Temp & Place(Count) & Rupees
If Count = 1 And Len(MyNumber) > 3 Then
MyNumber = Left(MyNumber, Len(MyNumber) - 3)
Else
If Count > 1 And Len(MyNumber) > 2 Then
MyNumber = Left(MyNumber, Len(MyNumber) - 2)
Else
MyNumber = ""
End If
End If
Count = Count + 1
Loop
Select Case Rupees
Case ""
Rupees = "No Rupees"
Case "One"
Rupees = "One Rupee"
Case Else
'****************************************************************
'Yogi Anand on 20-Sep-2003
'modified the following two lines to display "Rupees" to precede
' rem'd the first line and added the second line
'****************************************************************
'Rupees = Rupees & " Rupees"
Rupees = "Rupees " & Rupees

End Select
Select Case Paise
Case ""
'****************************************************************
'Yogi Anand on 20-Sep-2003
'modified the following two lines to display nothing for no paise
' rem'd the first line and added the second line
'****************************************************************

'Paise = " and No Paise"
'****************************************************************
'Yogi Anand on 03-Oct-2003
'modified the following line to display " Only" for no paise
' rem'd the first line and added the second line
'****************************************************************
'Paise = ""
Paise = " Only"
Case "One"
Paise = " and One Paisa"
Case Else
Paise = " and " & Paise & " Paise"

End Select
SpellIndian = Rupees & Paise
End Function
'*******************************************
' Converts a number from 100-999 into text *
'*******************************************
Function GetHundreds(ByVal MyNumber)
Dim Result As String
If Val(MyNumber) = 0 Then Exit Function
MyNumber = Right("000" & MyNumber, 3) 'Convert the hundreds place
If Mid(MyNumber, 1, 1) <> "0" Then
Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
End If
'Convert the tens and ones place
If Mid(MyNumber, 2, 1) <> "0" Then
Result = Result & GetTens(Mid(MyNumber, 2))
Else
Result = Result & GetDigit(Mid(MyNumber, 3))
End If
GetHundreds = Result
End Function
'*********************************************
' Converts a number from 10 to 99 into text. *
'*********************************************
Function GetTens(TensText)
Dim Result As String
Result = "" ' null out the temporary function value
If Val(Left(TensText, 1)) = 1 Then ' If value between 10-19
Select Case Val(TensText)
Case 10: Result = "Ten"
Case 11: Result = "Eleven"
Case 12: Result = "Twelve"
Case 13: Result = "Thirteen"
Case 14: Result = "Fourteen"
Case 15: Result = "Fifteen"
Case 16: Result = "Sixteen"
Case 17: Result = "Seventeen"
Case 18: Result = "Eighteen"
Case 19: Result = "Nineteen"
Case Else
End Select
Else ' If value between 20-99
Select Case Val(Left(TensText, 1))
Case 2: Result = "Twenty "
Case 3: Result = "Thirty "
Case 4: Result = "Forty "
Case 5: Result = "Fifty "
Case 6: Result = "Sixty "
Case 7: Result = "Seventy "
Case 8: Result = "Eighty "
Case 9: Result = "Ninety "
Case Else
End Select
Result = Result & GetDigit _
(Right(TensText, 1)) 'Retrieve ones place
End If
GetTens = Result
End Function
'*******************************************
' Converts a number from 1 to 9 into text. *
'*******************************************
Function GetDigit(Digit)
Select Case Val(Digit)
Case 1: GetDigit = "One"
Case 2: GetDigit = "Two"
Case 3: GetDigit = "Three"
Case 4: GetDigit = "Four"
Case 5: GetDigit = "Five"
Case 6: GetDigit = "Six"
Case 7: GetDigit = "Seven"
Case 8: GetDigit = "Eight"
Case 9: GetDigit = "Nine"
Case Else: GetDigit = ""
End Select
End Function

"I am so stuppid in explain my post so please forget it".
Click to expand...

Dear Sir
I m using 2007 and i m not a programer but how do i can add this spellindian in my worksheet module and keep it permanetely another thing i need your help in mail merge of excel to excel in 2007 i have make a template in first and the data are in net sheet so how i can merge it pls send me your reply asap.

Thanks Regards
Dharmesh Rana
India
 
Dear Sir

I m using 2007 and i m not a programer but how do i can add this spellindian in my worksheet module and keep it permanetely another thing i need your help in mail merge of excel to excel in 2007 i have make a template in first and the data are in net sheet so how i can merge it pls send me your reply asap.



Thanks Regards

Dharmesh Rana

India
Click to expand...
(e-mail address removed)
 
Back
Top