[HELP] VBA Currency NumberFormat Error - Excel

Question

Hello,

I've a worksheet with many cells formated as currency and i want modify the currency format through a combobox,

First i use this code to get the inicial currency type/format,

Private Sub ComboBox1_DropButtonClick()

inicial = Me.ComboBox1.Value

Select Case inicial

Case "EUR"

    oldFormat = "#.##0 €"

Case "GBP"

    oldFormat = "[$£-809]#.##0"

Case "USD"

    oldFormat = "#.##0 [$USD]"

End Select

End Sub

The oldformat variable is a global variable,

Public oldformat As String

After that i want to do a find using oldformat variable and a replace using a newformat variable,

Private Sub ComboBox1_Change()
   
    Dim ws As Worksheet
    Dim newFormat As String

    'On Error Resume Next
  
newValue = Me.ComboBox1.Value

Select Case newValue

Case "EUR"

    newFormat = "#.##0 €"

Case "GBP"

    newFormat = "[$£-809]#.##0"

Case "USD"

    newFormat = "#.##0 [$USD]"

End Select

'Set rNextCell = Application.FindFormat

    For Each ws In ActiveWorkbook.Worksheets
    
     Application.FindFormat.Clear
     Application.FindFormat.NumberFormat = oldFormat
     Application.ReplaceFormat.Clear
     Application.ReplaceFormat.NumberFormat = newFormat
     
     ws.Cells.Replace What:="", Replacement:="", LookAt:=xlPart, SearchOrder _
     :=xlByRows, MatchCase:=False, SearchFormat:=True, ReplaceFormat:=True
    
    Next ws
End Sub

I read the new value through the user choice on the combobox.

But this not work at all, the variables oldformat and newformat receive the correct values but i got a error on,

Run-time error '1004': Application-defined or object-defined error

Application.FindFormat.NumberFormat = oldformat
Application.ReplaceFormat.NumberFormat = newFormat

Is there any way to pass the newformat and oldformat value to the Numberformat property?

Or someone have another away to do this?

Link for exemple file,

https://www.dropbox.com/s/sdyfbddxy08pvlc/Change\_Currency.xlsm

I apreciate any help, i m a little bit new on .

I apologize if there are any errors in English, is not my natural language.

Answer 1

The reason for this RTE could be that

a) your format is wrong (because the NumberFormat property must be in English format) and

b) the format did not exist in the file

And your code did not work, because the DropButtonClick event is fired twice, the 1st time when the drop down opens and the 2nd when closes.

Try the code below.

Andreas.

Option Explicit

Dim OldFormat As String, NewFormat As String

Private Function GetFormat(ByVal Value) As String

  Select Case UCase(Value)

    Case "EUR"

      GetFormat = "#,##0 $"

    Case "GBP"

      GetFormat = "[$£-809]#,##0"

    Case "USD"

      GetFormat = "#,##0 [$USD]"

  End Select

End Function

Private Sub ComboBox1_GotFocus()

  'Get the old format

  OldFormat = GetFormat(ComboBox1.Value)

End Sub

Private Sub ComboBox1_LostFocus()

  'Replace the format when the user leaves the combo box

  Dim Ws As Worksheet

  'Get the new format

  NewFormat = GetFormat(ComboBox1.Value)

  'Be sure we valid have a format, otherwise we'll get a RTE

  If OldFormat = "" Then Exit Sub

  If NewFormat = "" Then Exit Sub

  'Maybe the format did not exist and must be added first

  On Error GoTo ApplyFormat

  'Prepare the search

  With Application.FindFormat

    .Clear

    .NumberFormat = OldFormat

  End With

  With Application.ReplaceFormat

    .Clear

    .NumberFormat = NewFormat

  End With

  'Replace all

  For Each Ws In Worksheets

    Ws.UsedRange.Replace "", "", SearchFormat:=True, ReplaceFormat:=True

  Next

  Exit Sub

ApplyFormat:

  'Apply each format once

  Dim SaveFormat As String

  On Error GoTo ExitPoint

  SaveFormat = ActiveCell.NumberFormat

  ActiveCell.NumberFormat = GetFormat("EUR")

  ActiveCell.NumberFormat = GetFormat("GBP")

  ActiveCell.NumberFormat = GetFormat("USD")

  ActiveCell.NumberFormat = SaveFormat

  'Now try again

  Resume

ExitPoint:

End Sub

Answer 2

It works like you said, my bad. :)

Thank you so much for your help and for your patience.

Regards,

Ricardo

Answer 3

So, i use your code and get this error

"excel cannot complete this task with available resources"

And nothing happens.

Then you did something wrong, here is your modified sample file:

https://dl.dropboxusercontent.com/u/35239054/Samples/ca55e147-fce9-4182-80d6-c6e3b2a80d26.xlsm

Andreas.

Answer 4

Sorry for the delay,  was on vacations.

So, i use your code and get this error

"excel cannot complete this task with available resources"

And nothing happens.