Hi,

I want to customise a number format such that it use the text of a
particular cell from a different sheet, e.g. R_text from cell_0001.

Can a macro do this?

Thanks,

Nick

If you want to, yes. I'm not entirely sure what you mean by a format like
"R_text from cell_0001" (the syntax looks a bit odd to me), but it's
definitely possible to set a particular number format using VBA code.

Try typing for example 123 or -123 into cell A2, and for example:

\A\B\C $#,##0.00_);[Red]($#,##0.00)

....into cell A1, then run the VBA lines below

Public Sub FormatTest()
' Get data from cell A1
Dim sFormat As String
sFormat = Worksheets("Sheet1").Cells(1, 1).Value

' ...fiddle a little bit more with sFormat here, if you want to

' Format cell A2
Worksheets("Sheet1").Cells(1, 2).NumberFormat = sFormat
End Sub

For more info about cell formatting, please open up the VBA help and search
for terms such as Format Codes, NumberFormat, Format Codes, NumberFormatLocal
etc.

Hope this helps,
/MP

"Nick Smith" wrote:

Hi,

I want to customise a number format such that it use the text of a
particular cell from a different sheet, e.g. R_text from cell_0001.

Can a macro do this?

Thanks,

Nick

Okay, sorry, I'll try and be more clear. I have a workbook for my project
managers which contains a front sheet where they list some key project
identifiers, one of which is a financial code, for example: "ETL06:9874". I
want to then use the number they place in here as part of the number format
for a column in another sheet.

So, in the next sheet, the number format would be defined as
"Risk_ETL06:9874_0001" and "Risk_ETL06:9874_0002" etc.... as the column goes
down.

I was going to include a button next to the front sheet where they enter the
financial code which would run a macro to assign the required number format
in the columns of the other sheet. I don't know how to have code constantly
running in the background without using a macro that runs as commanded.

Any thoughts?

Thanks,

Nick

"Mat P:son" wrote:

If you want to, yes. I'm not entirely sure what you mean by a format like
"R_text from cell_0001" (the syntax looks a bit odd to me), but it's
definitely possible to set a particular number format using VBA code.

Try typing for example 123 or -123 into cell A2, and for example:

\A\B\C $#,##0.00_);[Red]($#,##0.00)

...into cell A1, then run the VBA lines below

Public Sub FormatTest()
' Get data from cell A1
Dim sFormat As String
sFormat = Worksheets("Sheet1").Cells(1, 1).Value

' ...fiddle a little bit more with sFormat here, if you want to

' Format cell A2
Worksheets("Sheet1").Cells(1, 2).NumberFormat = sFormat
End Sub

For more info about cell formatting, please open up the VBA help and search
for terms such as Format Codes, NumberFormat, Format Codes, NumberFormatLocal
etc.

Hope this helps,
/MP

"Nick Smith" wrote:

Hi,

I want to customise a number format such that it use the text of a
particular cell from a different sheet, e.g. R_text from cell_0001.

Can a macro do this?

Thanks,

Nick

"Nick Smith" wrote:

Okay, sorry, I'll try and be more clear. I have a workbook for my project
managers which contains a front sheet where they list some key project
identifiers, one of which is a financial code, for example: "ETL06:9874". I
want to then use the number they place in here as part of the number format
for a column in another sheet.

Okay. But, in fact, I don't think you have to use the Number Format feature
at all here -- that's primarily used for, well, formatting numbers :o)
However, I suppose you can just as well concatenate some strings together and
put them straight into the cells as cell values.

So, in the next sheet, the number format would be defined as
"Risk_ETL06:9874_0001" and "Risk_ETL06:9874_0002" etc.... as the column goes
down.

That's fair

I was going to include a button next to the front sheet where they enter the
financial code which would run a macro to assign the required number format
in the columns of the other sheet. I don't know how to have code constantly
running in the background without using a macro that runs as commanded.

Hmmm, so you want the managers to input some data in a few cells, and then
use that data in other cells. No real need to use macros at all then, just
use e.g. the CONCATENATE() function, and fill out as many rows you feel like.

Or, if you really want to use VBA, then put this for example in the
ThisWorkbook module:

==========================

Option Explicit

Private Const RowCount As Integer = 100

Private Sub FillOutRows()
' Grab project id from Sheet1, cell A1
Dim sPrj As String
sPrj = Worksheets("Sheet1").Cells(1, 1).Value

' Iterate over cells A1 to A<RowCount in Sheet2,
' fill in the Risk id:s (incremental) for every cell.
Dim iRow As Integer
For iRow = 1 To RowCount
Worksheets("Sheet2").Cells(iRow, 1).Value = _
"Risk_" & _
sPrj & _
Format(iRow, "_0000")
Next iRow
End Sub

==========================

Any thoughts?

Thanks,

Nick

"Mat P:son" wrote:

If you want to, yes. I'm not entirely sure what you mean by a format like
"R_text from cell_0001" (the syntax looks a bit odd to me), but it's
definitely possible to set a particular number format using VBA code.

Try typing for example 123 or -123 into cell A2, and for example:

\A\B\C $#,##0.00_);[Red]($#,##0.00)

...into cell A1, then run the VBA lines below

Public Sub FormatTest()
' Get data from cell A1
Dim sFormat As String
sFormat = Worksheets("Sheet1").Cells(1, 1).Value

' ...fiddle a little bit more with sFormat here, if you want to

' Format cell A2
Worksheets("Sheet1").Cells(1, 2).NumberFormat = sFormat
End Sub

For more info about cell formatting, please open up the VBA help and search
for terms such as Format Codes, NumberFormat, Format Codes, NumberFormatLocal
etc.

Hope this helps,
/MP

"Nick Smith" wrote:

Hi,

I want to customise a number format such that it use the text of a
particular cell from a different sheet, e.g. R_text from cell_0001.

Can a macro do this?

Thanks,

Nick

Oh, sorry, I forgot:

The VBA code can be run as part of your button's Click event, or you can use
a Worksheet Change event to listen for changes to the particular cells you
expect your end users to change (the Target param of the Change event tells
you which cell(s) have been modified).

Cheers,
/MP

"Mat P:son" wrote:



"Nick Smith" wrote:

Okay, sorry, I'll try and be more clear. I have a workbook for my project
managers which contains a front sheet where they list some key project
identifiers, one of which is a financial code, for example: "ETL06:9874". I
want to then use the number they place in here as part of the number format
for a column in another sheet.

Okay. But, in fact, I don't think you have to use the Number Format feature
at all here -- that's primarily used for, well, formatting numbers :o)
However, I suppose you can just as well concatenate some strings together and
put them straight into the cells as cell values.

So, in the next sheet, the number format would be defined as
"Risk_ETL06:9874_0001" and "Risk_ETL06:9874_0002" etc.... as the column goes
down.

That's fair

I was going to include a button next to the front sheet where they enter the
financial code which would run a macro to assign the required number format
in the columns of the other sheet. I don't know how to have code constantly
running in the background without using a macro that runs as commanded.

Hmmm, so you want the managers to input some data in a few cells, and then
use that data in other cells. No real need to use macros at all then, just
use e.g. the CONCATENATE() function, and fill out as many rows you feel like.

Or, if you really want to use VBA, then put this for example in the
ThisWorkbook module:

==========================

Option Explicit

Private Const RowCount As Integer = 100

Private Sub FillOutRows()
' Grab project id from Sheet1, cell A1
Dim sPrj As String
sPrj = Worksheets("Sheet1").Cells(1, 1).Value

' Iterate over cells A1 to A<RowCount in Sheet2,
' fill in the Risk id:s (incremental) for every cell.
Dim iRow As Integer
For iRow = 1 To RowCount
Worksheets("Sheet2").Cells(iRow, 1).Value = _
"Risk_" & _
sPrj & _
Format(iRow, "_0000")
Next iRow
End Sub

==========================

Any thoughts?

Thanks,

Nick

"Mat P:son" wrote:

If you want to, yes. I'm not entirely sure what you mean by a format like
"R_text from cell_0001" (the syntax looks a bit odd to me), but it's
definitely possible to set a particular number format using VBA code.

Try typing for example 123 or -123 into cell A2, and for example:

\A\B\C $#,##0.00_);[Red]($#,##0.00)

...into cell A1, then run the VBA lines below

Public Sub FormatTest()
' Get data from cell A1
Dim sFormat As String
sFormat = Worksheets("Sheet1").Cells(1, 1).Value

' ...fiddle a little bit more with sFormat here, if you want to

' Format cell A2
Worksheets("Sheet1").Cells(1, 2).NumberFormat = sFormat
End Sub

For more info about cell formatting, please open up the VBA help and search
for terms such as Format Codes, NumberFormat, Format Codes, NumberFormatLocal
etc.

Hope this helps,
/MP

"Nick Smith" wrote:

Hi,

I want to customise a number format such that it use the text of a
particular cell from a different sheet, e.g. R_text from cell_0001.

Can a macro do this?

Thanks,

Nick