PDA

View Full Version : write in cells in correct order



JmE
03-07-2007, 01:58 AM
Hi!
i have a problem.. i want to do so the user who types somthing in the excel doc need to typ in the cells in the correct order..
so just the first cell are yellow and unlockt and when they have typed somting in it the next one unlock and switch color from red to yellow.

tnx for the help (:

JimmyTheHand
03-07-2007, 02:42 AM
Hi JmE :hi:

I've got two problems with your question.
1. Merged cells. I can't tell lots about it, but any expert here says that merged cells are killers of macros (and of programmers :bug:).
2. You don't specify the order of cells. Which comes after which.

Anyway, I'd like to recommend an alternative. How about using textbox controls, instead? See attachment for an example.

In case you have only 5 cells to fill, this could a be very simple alternative, and no need for merged cells. Filling a textbox enables the next one. If needed, then filling the last textbox copies data from textboxes to cells. But maybe you don't even have to copy the data to cells, because it is available for further process, anyway.

What do you think?

Jimmy

JmE
03-07-2007, 03:10 AM
Hi JmE :hi:

I've got two problems with your question.
1. Merged cells. I can't tell lots about it, but any expert here says that merged cells are killers of macros (and of programmers :bug:).
2. You don't specify the order of cells. Which comes after which.

Anyway, I'd like to recommend an alternative. How about using textbox controls, instead? See attachment for an example.

In case you have only 5 cells to fill, this could a be very simple alternative, and no need for merged cells. Filling a textbox enables the next one. If needed, then filling the last textbox copies data from textboxes to cells. But maybe you don't even have to copy the data to cells, because it is available for further process, anyway.

What do you think?

Jimmy



hmmm... :P but how to do it whith cells if there is no merged cells? (:
cus i think its much easeys to use cells in my excel calculations :P
never used textboxes before..
the order is from the first to the last red cell..



tnx

Charlize
03-07-2007, 03:44 AM
hmmm... :P but how to do it whith cells if there is no merged cells? (:
cus i think its much easeys to use cells in my excel calculations :P
never used textboxes before..
the order is from the first to the last red cell..

tnxTry this link : http://vbaexpress.com/kb/getarticle.php?kb_id=364

Charlize

JimmyTheHand
03-07-2007, 03:56 AM
I got it to work with merged cells... but still... :think:

JmE
03-07-2007, 05:06 AM
I got it to work with merged cells... but still... :think:

is there easyer to make some kind of warning message box?
like if not all required cells are filld in on save its popsup and all the empty required cells get red? and its abort the save? :P


tnx alot (:

JimmyTheHand
03-08-2007, 03:12 AM
Yes, it's easier. But your original idea worked as well, didn't it? Was it not quite what you needed?

Anyway, I revamped the previous code a little, though even so it's not fool-proof yet... and added the warning message option before save.

Jimmy

JmE
03-08-2007, 07:47 AM
Yes, it's easier. But your original idea worked as well, didn't it? Was it not quite what you needed?

Anyway, I revamped the previous code a little, though even so it's not fool-proof yet... and added the warning message option before save.

Jimmy



nice that was exactly what i needed (: but just a litle problem :P hehe how to save it if i want that on save macro and want the cells blank? :P

JimmyTheHand
03-08-2007, 08:10 AM
1.) Create a 2nd (blank) workbook, and put these two macros in there:
Sub Events_Off
Application.EnableEvents = False
End Sub

Sub Events_On
Application.EnableEvents = True
End Sub
2.) Run Events_Off macro to disable BeforeSave event of all workbooks. (Actually, this macro disables all events.)

3.) Save workbook #1 (the one you can't save otherwise).

4.) Switch to workbook #2 and run Events_On macro to re-enable events.


Edit:
On 2nd thought, you might want to add a prompting question, like "Some required cells are empty. Are you sure you want to save the workbook?"
and save it only if user answers "Yes".

lucas
03-08-2007, 03:12 PM
Seems like this whole thing would be handled better with a userform for input to a template.....

bigdoggit
03-08-2007, 09:30 PM
I was looking at the link to set tab entry for worksheet during data entry, and was trying to follow code, sort of a way to keep up on stuff and get new ideas. I can't figure out why the code jumps around the way it does. In the sheet module code, at the very end,

If targ Is Nothing Then
addr = Target.Cells(1, 1).Address(ColumnAbsolute:=False, RowAbsolute:=False)
X = Application.Match(addr, TabOrder, 0)
If IsError(X) Then Range(TabOrder(LBound(TabOrder))).Activate
Else
targ.Activate
End If why willl the code decide that targ is not Nothing and goto the else statement then jump back up to the if targ is Nothing then all over again?