Excel - Insert a decimal point using a visual keypad

[JeffGates]

First time posting, with a problem that I can't figure out.

I'm trying to create an excel based front end for my business and learing VBA as I go. I've created a visual keypad with numbers 0 through 9, a button to clear the input cell and a decimal point.

I've got everything working with the exception of the decimal point with the following code:

Sub Keypad1()
Dim Value1 As Integer
Value1 = 1
Cells(2, 2).Value = Cells(2, 2).Value & Value1
End Sub

I've read all of the FAQ and searched the internet for a week.
I tried "SendKeys" from the FAQ section but can't figure out how to get it to work with data that's already in the input cell.

Your advice is much appreciated.

 

[firefytr]

Hi, I'm really not sure what the code you posted is supposed to do. I'm not sure why you are concatenating the values (with the ampersand "&" sign), or did you really mean to add them??

A decimal point is merely part of the format. Change the format and you can add/minus a decimal point whenever you want. If you need the code, you can record a macro to see how it's done. The property is the .NumberFormat property.

HTH

-----------
Regards,
Zack Barresse

 

[macropod]

Hi Jeff,

If your code is meant to progressively populate Cell B2 as each character on the keypad is selected, then you're not going to be able to input the decimal point using an integer - which is what you've defined Value1 as.

As for the sub you posted, it appears to me to be designed to append '1' to the existing value in B2. To add a decimal point, you could use something like:

Sub Keypad1()
Cells(2, 2).Value = Cells(2, 2).Value & "1"
End Sub

Sub Keypad2()
Cells(2, 2).Value = Cells(2, 2).Value & "2"
End Sub

etc, for your numbers (note the abbreviated coding), then

Sub KeypadPoint()
Cells(2, 2).Value = "'" & Cells(2, 2).Value & "" & "."
End Sub

and

Sub KeypadEnter()
Cells(2, 2).Value = Cells(2, 2).Value
End Sub

The KeypadPoint sub (temporarily) converts the input to a string, so that the decimal point can stay put until another value is entered.

The final sub (KeypadEnter) is there just in case the user inserts the decimal point before realising there's no decimal values to input.

Cheers

[MS MVP - Word]

 

[firefytr]

Hi macropod,

I'm not sure I see the benefit of using another routine to change what is done when you can use a single routine to get the job done. (Not that I haven't used that method before.) Something like this should probably be done with a UserForm anyway. Also, John Walkenbach has already made a toolbar calculator, found here:

http://www.j-walk.com/ss/excel/files/toolbarcalc.htm

-----------
Regards,
Zack Barresse

 

[macropod]

Hi firefytr,

I agree with your comments about the userform, but using JW's calculator would necessitate distributing the xla with the worksheet - this way it's self-contained.

Cheers

[MS MVP - Word]

 

[firefytr]

The code is open-source. Just copy/paste!

-----------
Regards,
Zack Barresse

 

[JeffGates]

Thanks for the help folks.
I think that I've got it working.