excel vba - NumberFormat VBA With User-Defined Data -

i working on workbook has set number of template calculations used multiple times across scope of copied workbook. form object created user enter identifier each calculation set meets specific format. follows "aaa-##0.00" 'aaa' string of 1 3 letters. form has combo box cleared , populated on activation contains 258, customer-provided text possibilities (to reduce probable user error) text box adjacent entering digits. when "execute" button clicked, identifies selected calculations required user form, copies template worksheet(s) end of workbook, , populates identifier information @ bottom of list on contents page. here relevant code:

dim prefix string dim locmp double  'the digits entered in txtlocmp locmp = val(txtlocmp.value)  'mpprefixlst combo box, 'prefix' being desired custom format prefix = mpprefixlst.text & "-#0.00" 

when debugging, values stored in 'locmp' , 'prefix' appear satisfactory locmp being number , 'prefix' being desired format string. here, i've tried couple different options. first,

with contents 'contents type worksheet, loccount number of entries in contents 'including offset header    .unprotect           'other unrelated functions    .range("c" & loccount).value = format(locmp, prefix)           'other unrelated functions    .protect end 

the second,

with contents           'other unrelated functions    .range("c" & loccount)       .value = locmp       .numberformat = prefix    end           'other unrelated functions end 

neither of these options, though both make sense me, produce desired results. example, if provided text 'mpprefixlst' "sfe" 'prefix' value shows equal "sfe-#0.00" expected. when value of 555.44 entered locmp result in changed cell "36fe-#0.00" when desired result "sfe-555.44"! have searched on site (and others) approximate solution make fit, , msdn website no @ all. appreciated!

put backslash between each character:

sub test() cells(1, 1).numberformat = getfixedprefix("sae-") & "#0.00" end sub  function getfixedprefix(prefix string) string dim x, fixedprefix fixedprefix = "" x = 1 len(prefix)     fixedprefix = fixedprefix & "\" & mid(prefix,x,1) next x getfixedprefix = fixedprefix end function 

this makes numberformat = \s\f\e\-#0.00 should display correctly:

read more here

backslash. character appearing after backslash \ display literal, though may reserved operator (say, %). number 0.75 format code #.00% format 75.00%, format code #.00\% format .75%, ie. format code not use % operator literal.