VBA - Format how code returns value of Named Range - Excel 2010

[Johnny Thunder]

Hello All,

I am working with a userform and need to have a named range value returned as a format number.

Currently my named range ("PrgB") returns the value = 51.515151

I need to have this format as a 2 digit number = 51 but cant seem to get this done. Any help is appreciated!

my partial code

Sub Code()


Dim pctCompl As Single
Dim Nm1 As Range


Set Nm1 = Range("PrgB")


MsgBox Nm1

 

[Joe4]

Re: VBA Help - Format how code returns value of Named Range - Excel 2010

You can use the INT function to chop off the decimal portion, i.e.

MsgBox INT(Nm1)

 

[pgc01]

Re: VBA Help - Format how code returns value of Named Range - Excel 2010

Currently my named range ("PrgB") returns the value = 51.515151

I need to have this format as a 2 digit number = 51 but cant seem to get this done. Any help is appreciated!

MsgBox Nm1

Hi

Not clear.

You want
- to chop off de decimals 51.51 -> 51
- round to integer 51.51 -> 52
- integer always 2 digits 5.51 -> 05

??

Please clarify.

 

[Johnny Thunder]

Re: VBA Help - Format how code returns value of Named Range - Excel 2010

Thanks for the response @pgc01 - I would like the format to just trim off the decimals to always show the value as a whole number. Hope that clarifies?

Hi

Not clear.

You want
- to chop off de decimals 51.51 -> 51
- round to integer 51.51 -> 52
- integer always 2 digits 5.51 -> 05

??

Please clarify.

 

[Joe4]

Re: VBA Help - Format how code returns value of Named Range - Excel 2010

I would like the format to just trim off the decimals to always show the value as a whole number. Hope that clarifies?

Did you see my response above?
I believe I showed you how to do precisely that.

 

[Johnny Thunder]

Re: VBA Help - Format how code returns value of Named Range - Excel 2010

Hi @Joe4 , I tried your response and although when I enter it as a msgbox I get the desired result but when I enter it as part of the running code to execute nothing happens? The intent is to update a progress bar with this number.

This thing is kicking my butt since I have never done this type of code.

Sub Code()


Dim pctCompl As Single
Dim Nm1 As Range


Set Nm1 = Range("PrgB")


Nm2 = Int(Nm1)


pctCompl = Nm2


UserForm1.Text.Caption = pctCompl & "% Completed"
UserForm1.Bar.Width = pctCompl * 2


DoEvents


End Sub

Did you see my response above?
I believe I showed you how to do precisely that.

 

[Johnny Thunder]

Re: VBA Help - Format how code returns value of Named Range - Excel 2010

Finally, got it to work. your fix worked fine, I was just putting it in the wrong place.

Sub Code()


Dim pctCompl As Single
Dim Nm1 As Range


Set Nm1 = Range("PrgB")


pctCompl = Int(Nm1)


UserForm1.Text.Caption = pctCompl & "% Completed"
UserForm1.Bar.Width = pctCompl * 2


DoEvents


End Sub

Did you see my response above?
I believe I showed you how to do precisely that.