[RESOLVED] Excel: Can this be simplified?

[krtxmrtz]

Is there a shorter / more direct way to do this?

Sub TestSub()
   'TestCell is the name of a specific
   'cell containing input data
    x = ThisWorkbook.Names("TestCell")
    y = Range(x).Value
    Cells(12,9).Value = y*y
End Sub

[DKenny]

What is your definition of "input data"? I.e what are you trying to test for?

[krtxmrtz]

What is your definition of "input data"? I.e what are you trying to test for?

For example, the user inputs a numeric value in the cell with name "TestCell".

To summarize my recent VBA related posts, what I'm trying to do -the entire Excel project I'm currently working on- is a spreadsheet where the user is required to input numerical data to a number of cells and a numerical result is given after a number of calculations are performed involving both math formulas, interpolation in lookup tables, macros, etc.

I'm still in the interface design phase. This means that occasionally I realize I must allow for additional input just above (or to the left) of a cell that's being referenced in another cell or in a macro statement. So every now and then I must insert rows or columns. That's why I need to use a trick to forget about updating all these references manually in the macros or in the cells.

I want to protect the spreadsheet from being tampered with and at the same time I want it to be very user-friendly (that's why I'd like to tab around the allowed cells only).

In one word, I'm aiming toward perfection but I'm taking great pains because I've seldom used VBA in the past. In fact, whenever I have to undertake a project like this I'm always hesitating and spend plenty of time trying to decide whether I'm going to use a spreadsheet or a stand-alone VB application. Maybe I should have chosen the latter option in view of the difficulties I'm having.

[DKenny]

The easiest way to ensure that a use entrers a numeric value into a cell is to not use code at all!
Have a look at the 'Validation' command on the 'Data' menu. I use this built-in data validation on all my Excel apps, its easy to configure and implement. Have a look at the help file entry for "Prevent invalid data entry in a worksheet"

[Ecniv]

Another option (probably a bit late now) is to use a userform...


As for the question - did you need the user to enter a second item - or just have somewhere to hold a value? if the latter, store in a variable?

[krtxmrtz]

The easiest way to ensure that a use entrers a numeric value into a cell is to not use code at all!
Have a look at the 'Validation' command on the 'Data' menu. I use this built-in data validation on all my Excel apps, its easy to configure and implement. Have a look at the help file entry for "Prevent invalid data entry in a worksheet"

Well, I didn't mean exactly that. The spreadsheet doesn't have to be foolproof because the user knows what type / format of data must be used. The sheet protection I need is for those cells having formulas. In an earlier and less sophisticated version I made some time ago I would sometimes type to the wrong cell and delete important information.

[krtxmrtz]

Another option (probably a bit late now) is to use a userform...

In this case I'd probably do it in pure VB.

[zaza]

You could use 1 line:

Cells(12,9).Value = (Range("TestCell").value)^2

zaza

[krtxmrtz]

You could use 1 line:

Cells(12,9).Value = (Range("TestCell").value)^2

zaza

You're right...