PDA

View Full Version : Cell formula question



vassili
07-26-2007, 12:37 AM
my code writes the following string the corresonding cell of a order form:

Range("I" & i).Formula = " Units"

after the order form generates, a person needs to fill in the cell with an appropriate number. however, in testing, it's inconvenient to go into the formula bar and move the cursor to the front of " Units" before typing in a #.

if you just type directly into the cell, "Units" will obviously be replaced. how can i make it so that when the person enters a # directly into the cell, "Units" will be automatically generated behind that number.

i thought about using 2 seperate cells so "Units" can reside in it's own cell, but at this point, it would mean lost time going back to the code and incrementing a bunch of variables.

this process should be transparent to the user, so a user form/input box is a no go.

xld
07-26-2007, 12:45 AM
In your code that initialises those cells, preced it with

Application.EnableEvents = False

and reset back to True at the end. Then add this as worksheet event code, which means that it needs to be placed in the appropriate worksheet code module, not a standard code module.

To do this, right-click on the sheet tab, select the View Code option from the menu, and paste the code in.



Option Explicit

Private mPrev

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "I:I" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
.Value = .Value + mPrev
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
mPrev = Target
End Sub

vassili
07-26-2007, 12:58 AM
In your code that initialises those cells, preced it with

Application.EnableEvents = False

and reset back to True at the end. Then add this as worksheet event code, which means that it needs to be placed in the appropriate worksheet code module, not a standard code module.

To do this, right-click on the sheet tab, select the View Code option from the menu, and paste the code in.



Option Explicit

Private mPrev

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "I:I" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
.Value = .Value + mPrev
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
mPrev = Target
End Sub


wait, that means the user has to paste the code in? the main program generates the form at user's request and then the user enters the #s. the forms will be different names everytime depending on whatever the user uses it to be. can i get it to paste the code into the sheet automatically??

xld
07-26-2007, 01:30 AM
Why? You would setup that worksheet with the code.

rory
07-26-2007, 02:44 AM
You could change the number format:
With Range("I" & i)
.Formula = " Units"
.NumberFormat = "0 ""units"";-0;0;@"
End With
Regards,
Rory

unmarkedhelicopter
07-26-2007, 09:47 AM
you caold always just format the cell as custom :- #" Units"