Custom format macro?

Can I write a macro to format a number?

I have a bunch of cells containing time intervals stored as floating
point numbers in units of days. These are not dates, but are stored in
the same format. Some examples:

   1          1 day
   2.5        2.5 days or 2 days, 12 hours
   0.125      3 hours
   0.020833   30 minutes

and so on.

These values are calculated. I would like to display them as 

   nnn.n u

where "nnn.n" is a floating point number formatted to 1 decimal place
and "u" is a character indicating the units (Y=years, D=days, H=hours,
M=minutes, S=seconds). The macro will chose the largest units that
have at least 1 digit on the left of the decimal place. For example:

   1          1.0D
   2.5        2.5D
   0.125      3.0H
   0.020833  30.0M
 730.5        1.5Y

I already have the macro code that I wrote for a VB project. My
problem is how to get Excel to use it to format the value in the cell.

I know I call pass the value to the macro and have it return a string
(text) result, but then that result is not available as a number in
other calculations. I'd like to keep the value in the cell as a number
and just apply the macro for format it.

-- 
0
invalid (293)
6/21/2007 8:39:41 PM
excel.newusers 15348 articles. 2 followers. Follow

8 Replies
781 Views

Similar Articles

[PageSpeed] 51

In case anyone is interested, here's the formatting macro:

'************************************************************************
'           Format Interval Function

' Formats an interval as nnn.nu

'   nnn.n = interval to 1 decimal place
'   u     = units: Y=years, D=days, H=hours, M=minutes, S=seconds

' Syntax: y = FmtInt(interval)

Public Function FmtInt(ByVal interval As Double) As String

Const TSYear As Double = 365.25      ' 1 year
Const TSDay As Double = 1            ' 1 day
Const TSHour As Double = TSDay / 24  ' 1 hour
Const TSMin As Double = TSHour / 60  ' 1 minute
Const TSSec As Double = TSMin / 60   ' 1 second
    
Dim result As String  'Intermediate value
Dim units As String   'Units (see above)

If interval >= TSYear Then
  result = interval / TSYear
  units = "Y"
ElseIf interval >= TSDay Then
  result = interval
  units = "D"
ElseIf interval >= TSHour Then
  result = interval / TSHour
  units = "H"
ElseIf interval >= TSMin Then
  result = interval / TSMin
  units = "M"
Else
  result = interval / TSSec
  units = "S"
End If
    
FmtInt = Format(result, "0.0") & units

End Function


-- 
0
invalid (293)
6/21/2007 10:04:23 PM
The problem is in showing the units as part of the result.  Excel sees 
something like "30.0M" in a cell and doesn't think it's numeric.
You can kind of get around that in a cell formula by using this to get rid 
of the units indicator (assumes 30.0M in A1, and formula in B1, or any other 
cell)
=Value(Left(A1,Len(A1)-1)
which will show just 30 in the cell with the formula.

Your next problem is to figure out what the resulting value is!  Is 30 now 
referring to Seconds, Minutes, Hours, Days or Years.  You may be best off 
writing yet another user defined function to take the displayed entry in such 
a cell and convert it to whatever units you may need for any math operations 
to be performed.  It might look something like the code below, and would be 
called (using example with the 30.0M in A1 above) as =ReverseFmtInt(A1)

But I see a loss of accuracy in going back and forth with these functions.  
An entry of 370 results in FmtInt returning 1.0Y, but a conversion using 
=ReverseFmtInt("1.0Y") would return 365.25 meaning that 4 and 3/4 days got 
lost in translation.

Public Function ReverseFmtInt(someTime As String) As Double
Const TSYear As Double = 365.25      ' 1 year
Const TSDay As Double = 1            ' 1 day
Const TSHour As Double = TSDay / 24  ' 1 hour
Const TSMin As Double = TSHour / 60  ' 1 minute
Const TSSec As Double = TSMin / 60   ' 1 second

Dim timeValue As Double
  
If Len(someTime) > 1 Then
  timeValue = Val(Left(someTime, Len(someTime) - 1))

  Select Case Right(someTime, 1)
    Case "Y"
      ReverseFmtInt = timeValue * TSYear
    Case "D"
      ReverseFmtInt = timeValue * TSDay
    Case "H"
      ReverseFmtInt = timeValue * TSHour
    Case "M"
      ReverseFmtInt = timeValue * TSMin
    Case "S"
      ReverseFmtInt = timeValue * TSSec
    Case Else
      ReverseFmtInt = 0 ' can't parse input
    End Select
Else
  ReverseFmtInt = 0 ' can't parse input
End If
End Function

"LurfysMa" wrote:

> In case anyone is interested, here's the formatting macro:
> 
> '************************************************************************
> '           Format Interval Function
> 
> ' Formats an interval as nnn.nu
> 
> '   nnn.n = interval to 1 decimal place
> '   u     = units: Y=years, D=days, H=hours, M=minutes, S=seconds
> 
> ' Syntax: y = FmtInt(interval)
> 
> Public Function FmtInt(ByVal interval As Double) As String
> 
> Const TSYear As Double = 365.25      ' 1 year
> Const TSDay As Double = 1            ' 1 day
> Const TSHour As Double = TSDay / 24  ' 1 hour
> Const TSMin As Double = TSHour / 60  ' 1 minute
> Const TSSec As Double = TSMin / 60   ' 1 second
>     
> Dim result As String  'Intermediate value
> Dim units As String   'Units (see above)
> 
> If interval >= TSYear Then
>   result = interval / TSYear
>   units = "Y"
> ElseIf interval >= TSDay Then
>   result = interval
>   units = "D"
> ElseIf interval >= TSHour Then
>   result = interval / TSHour
>   units = "H"
> ElseIf interval >= TSMin Then
>   result = interval / TSMin
>   units = "M"
> Else
>   result = interval / TSSec
>   units = "S"
> End If
>     
> FmtInt = Format(result, "0.0") & units
> 
> End Function
> 
> 
> -- 
> 
0
Utf
6/24/2007 3:59:00 PM
I have been thinking more about this and I suspect that the whole basis of 
your request is to get away from that loss of precision that I mentioned 
earlier.  Correct?

One way to do that would be to use two cells to display the result of your 
function: have it place the numeric result into the cell with the formula in 
it, formatted to display as 0.0, and then put the type time indicator into a 
cell one column over on the same row.  I don't know if this is an acceptable 
solution or not.

You'd need to modify your function FmtInt() to accept 3 parameters:
Function FmtInt(byVal interval as Double, anyRow as Long, anyColumn as Long) 
as Double

You'd call it from a sheet as 
= FmtInt(X5,Row(),Column())
while the X5 would be used same as you do now, the Row(),Column() parameters 
are always entered in that fashion.  That way they always provide the row and 
column values of the cell with the formula(s) in it(them).

Later your code would end with

  Cells(anyRow, anyColumn+1) = units ' puts YDHMS into next column
  Cells(anyRow, anyColumn).NumberFormat = "0.0"
  FmtInt = result
End Function

I haven't tested this yet, but seems to me it should work.

"LurfysMa" wrote:

> In case anyone is interested, here's the formatting macro:
> 
> '************************************************************************
> '           Format Interval Function
> 
> ' Formats an interval as nnn.nu
> 
> '   nnn.n = interval to 1 decimal place
> '   u     = units: Y=years, D=days, H=hours, M=minutes, S=seconds
> 
> ' Syntax: y = FmtInt(interval)
> 
> Public Function FmtInt(ByVal interval As Double) As String
> 
> Const TSYear As Double = 365.25      ' 1 year
> Const TSDay As Double = 1            ' 1 day
> Const TSHour As Double = TSDay / 24  ' 1 hour
> Const TSMin As Double = TSHour / 60  ' 1 minute
> Const TSSec As Double = TSMin / 60   ' 1 second
>     
> Dim result As String  'Intermediate value
> Dim units As String   'Units (see above)
> 
> If interval >= TSYear Then
>   result = interval / TSYear
>   units = "Y"
> ElseIf interval >= TSDay Then
>   result = interval
>   units = "D"
> ElseIf interval >= TSHour Then
>   result = interval / TSHour
>   units = "H"
> ElseIf interval >= TSMin Then
>   result = interval / TSMin
>   units = "M"
> Else
>   result = interval / TSSec
>   units = "S"
> End If
>     
> FmtInt = Format(result, "0.0") & units
> 
> End Function
> 
> 
> -- 
> 
0
Utf
6/25/2007 12:21:00 PM
On Mon, 25 Jun 2007 05:21:00 -0700, JLatham <HelpFrom @
Jlathamsite.com.(removethis)> wrote:

>I have been thinking more about this and I suspect that the whole basis of 
>your request is to get away from that loss of precision that I mentioned 
>earlier.  Correct?

It's not the basis of the request, but it is a requirement.

The real point is not to change the underlying data at all -- just
format it for printing. It's like if I put "39258" in a cell. If I
select the "General" format, I will see "39258". If I select the
Number format with 2 decimal places, I will see "39258.00". If I
select the Currency format, I will see "$39,258.00". And.... if I
select the Date format, I'll see "6/25". But no matter what format I
use, the contents of the cell remains "39258".

That's what I want here, except that I want Excel to run my function
and use the value it returns as the display value. I don't want to
change the underlying data at all.

I can't believe that Excel doesn't support this type of custom
formats. t would be trivial to implement.

>One way to do that would be to use two cells to display the result of your 
>function: have it place the numeric result into the cell with the formula in 
>it, formatted to display as 0.0, and then put the type time indicator into a 
>cell one column over on the same row.  I don't know if this is an acceptable 
>solution or not.

There are a lot of work-arounds.

>You'd need to modify your function FmtInt() to accept 3 parameters:
>Function FmtInt(byVal interval as Double, anyRow as Long, anyColumn as Long) 
>as Double
>
>You'd call it from a sheet as 
>= FmtInt(X5,Row(),Column())
>while the X5 would be used same as you do now, the Row(),Column() parameters 
>are always entered in that fashion.  That way they always provide the row and 
>column values of the cell with the formula(s) in it(them).
>
>Later your code would end with
>
>  Cells(anyRow, anyColumn+1) = units ' puts YDHMS into next column
>  Cells(anyRow, anyColumn).NumberFormat = "0.0"
>  FmtInt = result
>End Function
>
>I haven't tested this yet, but seems to me it should work.
>
>"LurfysMa" wrote:
>
>> In case anyone is interested, here's the formatting macro:
>> 
>> '************************************************************************
>> '           Format Interval Function
>> 
>> ' Formats an interval as nnn.nu
>> 
>> '   nnn.n = interval to 1 decimal place
>> '   u     = units: Y=years, D=days, H=hours, M=minutes, S=seconds
>> 
>> ' Syntax: y = FmtInt(interval)
>> 
>> Public Function FmtInt(ByVal interval As Double) As String
>> 
>> Const TSYear As Double = 365.25      ' 1 year
>> Const TSDay As Double = 1            ' 1 day
>> Const TSHour As Double = TSDay / 24  ' 1 hour
>> Const TSMin As Double = TSHour / 60  ' 1 minute
>> Const TSSec As Double = TSMin / 60   ' 1 second
>>     
>> Dim result As String  'Intermediate value
>> Dim units As String   'Units (see above)
>> 
>> If interval >= TSYear Then
>>   result = interval / TSYear
>>   units = "Y"
>> ElseIf interval >= TSDay Then
>>   result = interval
>>   units = "D"
>> ElseIf interval >= TSHour Then
>>   result = interval / TSHour
>>   units = "H"
>> ElseIf interval >= TSMin Then
>>   result = interval / TSMin
>>   units = "M"
>> Else
>>   result = interval / TSSec
>>   units = "S"
>> End If
>>     
>> FmtInt = Format(result, "0.0") & units
>> 
>> End Function
>> 
>> 
>> -- 
>> 


-- 
0
invalid (293)
6/26/2007 5:28:11 AM
Sorry, I didn't make the rules. <g>

Notice what you've explained here - referring to a value that starts out as 
just a number and then choosing from any of a variety of formats that each 
deals with the presentation of the appearance of that numeric value.  When 
you tack an "M" or "D" or other letter to the end of it, it's no longer a 
number - it becomes text.

Perhaps someone who knows more formatting trickery than I do will come to 
your rescue.

"LurfysMa" wrote:

> On Mon, 25 Jun 2007 05:21:00 -0700, JLatham <HelpFrom @
> Jlathamsite.com.(removethis)> wrote:
> 
> >I have been thinking more about this and I suspect that the whole basis of 
> >your request is to get away from that loss of precision that I mentioned 
> >earlier.  Correct?
> 
> It's not the basis of the request, but it is a requirement.
> 
> The real point is not to change the underlying data at all -- just
> format it for printing. It's like if I put "39258" in a cell. If I
> select the "General" format, I will see "39258". If I select the
> Number format with 2 decimal places, I will see "39258.00". If I
> select the Currency format, I will see "$39,258.00". And.... if I
> select the Date format, I'll see "6/25". But no matter what format I
> use, the contents of the cell remains "39258".
> 
> That's what I want here, except that I want Excel to run my function
> and use the value it returns as the display value. I don't want to
> change the underlying data at all.
> 
> I can't believe that Excel doesn't support this type of custom
> formats. t would be trivial to implement.
> 
> >One way to do that would be to use two cells to display the result of your 
> >function: have it place the numeric result into the cell with the formula in 
> >it, formatted to display as 0.0, and then put the type time indicator into a 
> >cell one column over on the same row.  I don't know if this is an acceptable 
> >solution or not.
> 
> There are a lot of work-arounds.
> 
> >You'd need to modify your function FmtInt() to accept 3 parameters:
> >Function FmtInt(byVal interval as Double, anyRow as Long, anyColumn as Long) 
> >as Double
> >
> >You'd call it from a sheet as 
> >= FmtInt(X5,Row(),Column())
> >while the X5 would be used same as you do now, the Row(),Column() parameters 
> >are always entered in that fashion.  That way they always provide the row and 
> >column values of the cell with the formula(s) in it(them).
> >
> >Later your code would end with
> >
> >  Cells(anyRow, anyColumn+1) = units ' puts YDHMS into next column
> >  Cells(anyRow, anyColumn).NumberFormat = "0.0"
> >  FmtInt = result
> >End Function
> >
> >I haven't tested this yet, but seems to me it should work.
> >
> >"LurfysMa" wrote:
> >
> >> In case anyone is interested, here's the formatting macro:
> >> 
> >> '************************************************************************
> >> '           Format Interval Function
> >> 
> >> ' Formats an interval as nnn.nu
> >> 
> >> '   nnn.n = interval to 1 decimal place
> >> '   u     = units: Y=years, D=days, H=hours, M=minutes, S=seconds
> >> 
> >> ' Syntax: y = FmtInt(interval)
> >> 
> >> Public Function FmtInt(ByVal interval As Double) As String
> >> 
> >> Const TSYear As Double = 365.25      ' 1 year
> >> Const TSDay As Double = 1            ' 1 day
> >> Const TSHour As Double = TSDay / 24  ' 1 hour
> >> Const TSMin As Double = TSHour / 60  ' 1 minute
> >> Const TSSec As Double = TSMin / 60   ' 1 second
> >>     
> >> Dim result As String  'Intermediate value
> >> Dim units As String   'Units (see above)
> >> 
> >> If interval >= TSYear Then
> >>   result = interval / TSYear
> >>   units = "Y"
> >> ElseIf interval >= TSDay Then
> >>   result = interval
> >>   units = "D"
> >> ElseIf interval >= TSHour Then
> >>   result = interval / TSHour
> >>   units = "H"
> >> ElseIf interval >= TSMin Then
> >>   result = interval / TSMin
> >>   units = "M"
> >> Else
> >>   result = interval / TSSec
> >>   units = "S"
> >> End If
> >>     
> >> FmtInt = Format(result, "0.0") & units
> >> 
> >> End Function
> >> 
> >> 
> >> -- 
> >> 
> 
> 
> -- 
> 
0
Utf
6/26/2007 11:10:01 PM
Hi

Surely you would be better off setting the format within your function, 
rather than appending a letter and leaving it till the end of the 
function.
e.g.
 If interval >= TSYear Then
 result = format(interval / TSYear,"dd/mm/yyyy")

etc.
-- 
Regards

Roger Govier


"LurfysMa" <invalid@invalid.invalid> wrote in message 
news:ta8183p118qrn682ino5p8nq0t78dgpjeh@4ax.com...
> On Mon, 25 Jun 2007 05:21:00 -0700, JLatham <HelpFrom @
> Jlathamsite.com.(removethis)> wrote:
>
>>I have been thinking more about this and I suspect that the whole 
>>basis of
>>your request is to get away from that loss of precision that I 
>>mentioned
>>earlier.  Correct?
>
> It's not the basis of the request, but it is a requirement.
>
> The real point is not to change the underlying data at all -- just
> format it for printing. It's like if I put "39258" in a cell. If I
> select the "General" format, I will see "39258". If I select the
> Number format with 2 decimal places, I will see "39258.00". If I
> select the Currency format, I will see "$39,258.00". And.... if I
> select the Date format, I'll see "6/25". But no matter what format I
> use, the contents of the cell remains "39258".
>
> That's what I want here, except that I want Excel to run my function
> and use the value it returns as the display value. I don't want to
> change the underlying data at all.
>
> I can't believe that Excel doesn't support this type of custom
> formats. t would be trivial to implement.
>
>>One way to do that would be to use two cells to display the result of 
>>your
>>function: have it place the numeric result into the cell with the 
>>formula in
>>it, formatted to display as 0.0, and then put the type time indicator 
>>into a
>>cell one column over on the same row.  I don't know if this is an 
>>acceptable
>>solution or not.
>
> There are a lot of work-arounds.
>
>>You'd need to modify your function FmtInt() to accept 3 parameters:
>>Function FmtInt(byVal interval as Double, anyRow as Long, anyColumn as 
>>Long)
>>as Double
>>
>>You'd call it from a sheet as
>>= FmtInt(X5,Row(),Column())
>>while the X5 would be used same as you do now, the Row(),Column() 
>>parameters
>>are always entered in that fashion.  That way they always provide the 
>>row and
>>column values of the cell with the formula(s) in it(them).
>>
>>Later your code would end with
>>
>>  Cells(anyRow, anyColumn+1) = units ' puts YDHMS into next column
>>  Cells(anyRow, anyColumn).NumberFormat = "0.0"
>>  FmtInt = result
>>End Function
>>
>>I haven't tested this yet, but seems to me it should work.
>>
>>"LurfysMa" wrote:
>>
>>> In case anyone is interested, here's the formatting macro:
>>>
>>> '************************************************************************
>>> '           Format Interval Function
>>>
>>> ' Formats an interval as nnn.nu
>>>
>>> '   nnn.n = interval to 1 decimal place
>>> '   u     = units: Y=years, D=days, H=hours, M=minutes, S=seconds
>>>
>>> ' Syntax: y = FmtInt(interval)
>>>
>>> Public Function FmtInt(ByVal interval As Double) As String
>>>
>>> Const TSYear As Double = 365.25      ' 1 year
>>> Const TSDay As Double = 1            ' 1 day
>>> Const TSHour As Double = TSDay / 24  ' 1 hour
>>> Const TSMin As Double = TSHour / 60  ' 1 minute
>>> Const TSSec As Double = TSMin / 60   ' 1 second
>>>
>>> Dim result As String  'Intermediate value
>>> Dim units As String   'Units (see above)
>>>
>>> If interval >= TSYear Then
>>>   result = interval / TSYear
>>>   units = "Y"
>>> ElseIf interval >= TSDay Then
>>>   result = interval
>>>   units = "D"
>>> ElseIf interval >= TSHour Then
>>>   result = interval / TSHour
>>>   units = "H"
>>> ElseIf interval >= TSMin Then
>>>   result = interval / TSMin
>>>   units = "M"
>>> Else
>>>   result = interval / TSSec
>>>   units = "S"
>>> End If
>>>
>>> FmtInt = Format(result, "0.0") & units
>>>
>>> End Function
>>>
>>>
>>> -- 
>>>
>
>
> -- 


0
roger5293 (1125)
6/26/2007 11:58:55 PM
On Tue, 26 Jun 2007 16:10:01 -0700, JLatham <HelpFrom @
Jlathamsite.com.(removethis)> wrote:

>Sorry, I didn't make the rules. <g>

I'm not sure anyone did.

>Notice what you've explained here - referring to a value that starts out as 
>just a number and then choosing from any of a variety of formats that each 
>deals with the presentation of the appearance of that numeric value.  

Yes, the presentation only, not the data itself.

>When 
>you tack an "M" or "D" or other letter to the end of it, it's no longer a 
>number - it becomes text.

Well, yes and no. The displayed value is text, but so is "10:12:45" or
"June 6" or "$12,000.00". Those are all built-in formats. They do not
change the data itself. They just format it.

I want to be able to format the data using my own macro and then give
that value to Excel to display, while leaving the underlying data
alone. 

I was hoping to be able to put something like "=MyFmt()" in the Custom
format field and have Excel call my function, pass it the cell data,
and use the results to display. Simple and powerful. I tried it and,
of course, it doesn't work.

I was hoping that there might be some hook or add-in to make that
work.

>Perhaps someone who knows more formatting trickery than I do will come to 
>your rescue.

I'm listening...

-- 
0
invalid (293)
6/29/2007 5:58:10 AM
On Wed, 27 Jun 2007 00:58:55 +0100, "Roger Govier"
<roger@technologyNOSPAM4u.co.uk> wrote:

>Hi
>
>Surely you would be better off setting the format within your function, 
>rather than appending a letter and leaving it till the end of the 
>function.

Huh? How so?

>e.g.
> If interval >= TSYear Then
> result = format(interval / TSYear,"dd/mm/yyyy")

The first part of that line is from an earlier version of the
function, which, I think, didn't work. The format pattern
("mm/dd/yyyy") will not do what I want. My datye values are
"intervals" (date2 - date1), not absolute dates. I will never want a
mm/dd/yy value.

Here's the latest version of the function:

Public Function FmtInt(ByVal interval As Double) As String

Const TSWeek As Double = 7           ' 1 week in days
Const TSDay As Double = 1            ' 1 day in days
Const TSHour As Double = TSDay / 24  ' 1 hour in days
Const TSMin As Double = TSHour / 60  ' 1 minute in days
Const TSSec As Double = TSMin / 60   ' 1 second in days
' Note: if a variable number of decimal places are needed,
'       this constant must be generated dynamically
Const FmtPat As String = "0.0"       ' The format function pattern

If Format(interval / TSSec, FmtPat) < 60 Then 
  FmtInt = Format(interval / TSSec, FmtPat) & "S"
ElseIf Format(interval / TSMin, FmtPat) < 60 Then
  FmtInt = Format(interval / TSMin, FmtPat) & "M"
ElseIf Format(interval / TSHour, FmtPat) < 24 Then
  FmtInt = Format(interval / TSHour, FmtPat) & "H"
ElseIf Format(interval, FmtPat) < 7 Then
  FmtInt = Format(interval, FmtPat) & "D"
Else
  FmtInt = Format(interval / TSWeek, FmtPat) & "W"
End If
    
End Function



-- 
0
invalid (293)
6/29/2007 6:10:55 AM
Reply:

Similar Artilces:

Zip codes are not formatting
I am trying to do a mail merge using an excel data source of addresses. The zip codes appear with zeroes in the data source, but when I merge them to MS Word, the zeroes are dropped. How do I fix this problem? There's an article on the Microsoft web site that might help you: Answer Box: Numbers don't merge right in Word http://office.microsoft.com/en-ca/assistance/HA011164951033.aspx Debbie Love wrote: > I am trying to do a mail merge using an excel data source of addresses. The > zip codes appear with zeroes in the data source, but when I merge them to MS > Word,...

Toolbar macros, Personal.xls and the .xlb file
Several of my toolbar buttons are associated with macros. Whenever I update the toolbars I copy my .xlb file to the other 2 or 3 machines I work on. However my Personal.xls file is not located in the same folder on each machine. Consequently after copying the new .xlb file across to the other machines, I have to re-point each button to the correct macro. Is there an easier way of doing this - eg. with some specific macro? Your life will become much simpler if you include code to create the toolbar when the workbook is opened and include code to destroy the toolbar when the workbook is closed...

Macro writing
I understand the concept of macros, I just don't know how they execute and the possible functions. I would like to import a considerable amount of data from excel into access. The problem I have is that the information I want to import for each record is on two adjacent lines. I need a macro that will move the data contained in just one cell to the end of the row above it and then delete the row that, that information was contained in. Then I need it to repeat that function on every other row. Sounds easy right? Way my luck runs you could do anything you want to do except what I need. Anyo...

Macro for test result Count as pass, fail and incomplete.
Hi all, Please send me a macro to resolve my issue as i have to excecute test cases and after that at the end of the day i need to send the number of count for my test cases as Pass, Fail and incomplete, and right now i'm doing this manually so please send me a macro so that just running that macro i 'll able to count my test result separatly as pass fail and incomplete... Thanx in advance... Amar You have given no information about the data layout, so it is difficult to be specific, but you can probably do what you want with a worksheet function instead of a macro. See Help fo...

chart resize macro #2
Hi all, I often have to put a large number of charts in a report that need to be the same size. I'd like to create a macro that automatically resizes selected charts rather than manually resizing each one. I've tried to record a macro where I select a chart and resize it but the resizing part doesn't seem to get recorded. Any idea on how I would do this? Thanks, Josh Sub ChartResize1024x768() Dim oCht As Chart Set oCht = ActiveChart oCht.Parent.Width = 768 '1024*.75=768 oCht.Parent.Height = 576 '768 *.75=576 End Sub Works perfectly. Thank...

Custom contact entities
Hi, I just started using CRM 3.0 and I would like to extend the Account entity by adding another contact lookup field. For each account I would like to have a main contact, a technical contact and then some. I've tried creating custom entities and I've gotten them to a form as a lookup but how can I have them lookup from the contacts? If someone knows of a tutorial for this case it would be greatly appreciated. Thanks, Valdimar Creating mutliple relationships between any 2 entities is not supported in v3.0. This is how the lookups are created. -- Matt Parks MVP - Mi...

How I write following macro?
Hi. I must prepare macro: that is steps open file (xml) finding in this file strings, that is defined in macro For found strings copy falue, that is in xml structure (beetwen > < for exammple: >100<) paste number in right cell (in to excel file) Open window: save as... thx Tom ...

converting the digits to indian currency format digit
I have the figure as below in one of my form field/and in report: 23,125,456.48 I want to display in indian currency format : 2,31,25,456.48 Any help, ugently requrired. Regards Irshad How about: Format([YourField],"##,##,##,##0,00") ...or some version of it? Irshad Alam wrote: >I have the figure as below in one of my form field/and in report: > >23,125,456.48 > >I want to display in indian currency format : > >2,31,25,456.48 > >Any help, ugently requrired. > >Regards > >Irshad -- RuralGuy (RG for sh...

Tools-macro-security
One of my clients cannot get the change security to low option to stick. Every time she opens the database, she gets assaulted by meaningless security messages because she cannot turn them off. Is this being controlled by the IT department with some registry setting? Pat: It COULD be a Group Policy that their SysAdmin has set. -- Bob Larson Access World Forums Super Moderator ____________________________________ If my post was helpful to you, please rate the post. "Pat Hartman" wrote: > One of my clients cannot get the change security to low option to stick. > Eve...

Change Full name format to First Last Middle ??
Hi, need to set the name format to First Last Middle but the CRM setting do not cater for that. can anyone assist in the setting? is it possible to add that option in? Thanks in advance On Feb 14, 1:25 pm, dreamerz <dream...@discussions.microsoft.com> wrote: > Hi, > > need to set the name format to First Last Middle > but the CRM setting do not cater for that. > can anyone assist in the setting? > is it possible to add that option in? > > Thanks in advance Hi, i m someone working on MS CRM from its first version. if u need any help on MS CRM, u can contact me...

macro with F9
I want to a macro to insert the formula =TODAY() and then recalc it to make it a static result that represents the date as of the time the macro is run, not a dynamic formula that would change with each new day. I have recorded a macro to do this. If I run the macro & today is Aug 3, the correct result would be 08/03/2005, not the formula =TODAY(). However, if I run my macro tomorrow, Aug 4, the macro returns a result of 08/03/2005, because the code that resulted from my macro-record has a line in it, such as Select Active Cell. Active Cell = "08/03/2005." I would like ...

Custom Sheet Look
I just came across a simple excel file on my PC that shows only the spreadsheet (Sheet1) from A1:G25 (Normal White area, with grid lines). The remainder on the screen is dark gray. How is this acheived? I can't seem to find the settings that are producing this view. TIA, Just hide all columns past G and all rows below 25 -- Gary's Student "JMay" wrote: > I just came across a simple excel file on my PC > that shows only the spreadsheet (Sheet1) from > A1:G25 (Normal White area, with grid lines). > The remainder on the screen is dark gray. > How is this a...

Spreadsheet for customer product form
Hi, I am new and this is my first post. So please forgive me in advance fo my ignorance but I only know the basics of Excel right now. I have bee searching the web for days and finally came across your forum. What I am trying to accomplish is I want customers to be able to selec from a long list of products. How do I make several drop down list where they can select a product o more than one product in the list and then the product they select wit the information they input on that row, like quantity etc. will b added to another spreadsheet, and that spread sheet the rows will g one aft...

Add Custom Field to an Account
I am trying to add a custom field to an 'Account' and don't see it appearing after restarting IIS. Here's what I did: Clicked on Goto > Settings > Customization > Customize Entities > Account > Attributes > New (FYI this is a date field called Renewal Date). After saving and publishing, I then restarted IIS on the CRM box, but don;t see the new field in any part of the Account sections. Thoughts? Thanks You will have to go back to the view of the account and add that field to the form. You can test the view. If it looks ok, then you publish the chang...

Customizing a Service
Hi Friends, How can I customize a Service? That is, I want a service(eg.Messenger Service/MSSQLSERVER Service) to be started and stopped by only a single user in a LAN. Let me clarify it: I have a machine in a LAN. I want the Messenger Service to be started and stopped by me only. i.e. if some other person in the same Domain in a LAN logged into my machine using his name ( NOT mine) and try to start or stop this Messenger Service he should NOT be able to do that. How can I make it so? I gave a try using the ChangeServiceConfig API. But failed!!! thnx, AK. *** ...

Format (excel 2002)
This is a multi-part message in MIME format. ------=_NextPart_000_0014_01C3E26C.86E36260 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable cell A1=3DJim (green) cell A2=3DBob (blue) cell A3=3DJill (red) In cell D10 Ihave: IF(B2>5,A1,A3) How do I get the original format (Color) of A1, A2 or A3 in D10 ?? Thanks. ------=_NextPart_000_0014_01C3E26C.86E36260 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> &l...

Conditional Format #3
Hi all, Can someone help me with this conditional format problem? I am trying to express the following: If the number in cell B3 can be found in the range J1:O1, then the condition is true. The number in B3 is constant (35), the values in range J1:O1 change weekly. Many thanks George Gee Use formula is and =COUNTIF(J1:O1,B3)>0 -- Regards, Peo Sjoblom "George Gee" <george.nomaps@ntlworld.xom> wrote in message news:e4uSSHSFFHA.3120@TK2MSFTNGP12.phx.gbl... > Hi all, > > Can someone help me with this conditional format problem? > > I am trying to ex...

Can't open files from a different version saved to 2000 format
Hi, I am having problems opening a file sent to me by someone using Publisher 2003. I have Publisher 2000. She saved to the 2000 format, but it says that I cannot open files from a different version. The document shows up in the preview area, but will not open. She even tried to save it to the 98 format, but it still will not open. Any ideas? Julie wrote: > Hi, > > I am having problems opening a file sent to me by someone using > Publisher 2003. I have Publisher 2000. She saved to the 2000 format, > but it says that I cannot open files from a different version. The > d...

Macro Warning #2
I've been working on a payroll spreadsheet, suddenly when I open it, I get the macro warning, but I haven't created any macros. Help? Are you using anything from the control toolbox? regardless, open the workbook, press alt + F11, in the project pane to the left check to see if you have any modules, if so right click it/them and select remove module, answer no when prompted, if there are no modules double click each sheet module and ThisWorkbook, delete whatever code might be in those, press alt + Q to close the VBE and then save the workbook. HTH -- Regards, Peo Sjoblom...

Make a Macro on a Toolbar
Hi, I created a Macro but I want to have it show up on a toolbar. I know that you can create a custom toolbar but how can i add a macro (which I would need an icon) onto that toolbar or any toolbar? Thanks, Sean Sean Tools>Customize>Commands. Scroll down to "Macros" and select the smi;ey-face button. Drag it up to an existing toolbar. Right-click on it and "assign macro". Choose your macro from the dialog box. Note: you can edit the the smiley-face button. There are many other ways to do what you want, the above is the basics. Gord Dibben Excel MVP On M...

Change Date By Macro or Code Leaving Only One Record
Hi, I have a table called RUNDATE which has only one record in the date which my whole program looks at as the running date. I would like to run a Macro on a form or a Code upon which once I press it that single date in that table to change to the next day, lets say from 3/5/07 to 4/5/07 to delete the previous record of the 3/5/07 and to be left only with one record with the new date in this case lets say 4/5/07. Much Appreciated make a query of this single record table field - and then put that query into your form as a subform.....you can keep it not visible if you wish......

Formatting Cells #14
Is it possible to format cells to accept text and display that text in title case? I am using Excel 2003. Not through formatting alone. You could use a helper cell: =upper(a1) Or you could use an event macro like the one at Chip Pearson's site: http://www.cpearson.com/excel/case.htm look for: Changing Case On Data Entry Paul wrote: > > Is it possible to format cells to accept text and display that text in title > case? I am using Excel 2003. -- Dave Peterson ...

Protected cells -automatically format to a different color
My old dos spreadsheet (supercalc 5) used to colour the text of protected cells in a different color ,(which made the entry of data easy because the protected cells usually contain formulae ).When reentering new data I was then able to select the whole spreadsheet and "blank " all cells that are not protected in one keystroke. This left all cells that were protected with there formulae intact and highlighted in a different color. Can anyone suggest the way to do this in EXCEL? The ideal solution would be to create a format or a style that will automatically be used whenever a cel...

Custom Number Formatting
I'm creating a form where you can enter a credit card number. I set a custom number for the cell and described it as ####-####-####-####. You can enter a 16 digit number just fine, but when you tab away from the cell and the custom number takes place, it changes the last digit to a "0". Has anyone else encountered this? How do I fix it? I was told I could add an apostrophe before the entry, but this form is going to be used by hundreds of employees, so it needs to work automatically. Suggestions? -- CSBUG ---------------------------------------------------------------------...

Global Customer Accounts don't reach other stores
Hi, Our Global Customer accounts created at Store A seemed to have stopped reaching Store B. We can see them in HQ, but they don't seem to be getting downloaded to the other store. Client is running. Not sure what's going on. Help! -- Thanks, Pete ...