• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA Log data Changes in Table

sswcharlie1

New Member


Hi

A very good post on above link re log of data.

I am in need something a lot more basic.

Have worksheet1 with tableA and worksheet2 with tableB .
tableA records data from userform (items in and items out), entries added and entries removed. Data from 1 to 50 rows varying.

tableB is to record any items in or out. Other items will remain in table.

more than 1 row could be changed each time from cut/paste action.

example single col., in rows of data
A
M
D
R
T

Then M & D removed. ( A second table will record this as item in)

Then
Z
99
43
added. ( another table will record the item out).

Log file ( ws2 tableB ) should then show changes

ColA ColB
Removed
M
D

Added
99
43
Z

Your comments will be appreciated.

Thanks
Charles Harris
NZ
 

sswcharlie1

New Member
Hi Vietm
Thanks for your earlier response. I have done as you asked and created a wb with examples etc. I learnt a lot in the meantime.
The wb is not working because there are some things I do not know as yet.
1. How can I F8 thru the code. I cannot seem to get it to work. BTW Excel 2016.
2. Using the event method I cannot work out how the dim sOld etc are used. How does the event know if removing or adding data?
3. I want to learn as I fix this, if you can help with this. Go easy on me, I am 76 years old and a bit slow!
4. I can respond to your questions.
VBA in wb attached
Thanks
Charles Harris
 

Attachments

vletm

Excel Ninja
Hello sswcharlie1
3) As You know - age is only a number ... as well as - no need to hurry!

I opened You file ...
I should guess - what are You doing?
I have some kind of image, but ... I'm not sure - not yet.

I checked Your code, but ... grammar ... grammar ...
You remember well, that there are grammar, how to write eg English-text. ( I do not! )
Excel has own grammar - sometimes very tight.
If the code has written correct, Excel would understand it - otherwise, something else - okay?

I modified some parts, but it won't work yet! Not at all!
Please, try to check to modifications ... and ... try to find out - why?

What next?
For short time - forget Excel and events - okay?
Try to figure, how to do Your idea manually?
After above, You could try to 'explain' that to Excel with VBA...
Still, do not use any events!
Try to write new code, which could so same as Your manually duties.
Every step matters!
= maybe You 'manual duty' could like...
You write to cell 111 and You'll press <ENT> ...hmm?
what should happen next?
what should happen next?
what should happen next?
what should happen next?
what should happen next? ... etc
Only You will know those.

Maybe, above text was something different than You ... expected,
but I'll try to help ...
'The River City'
 

Attachments

sswcharlie1

New Member
Hi vletm
Thanks for your previous post.
I have gone back as you suggested to basics. See attached.
Have set up some new tabs, red in color, for a very basic timetable etc.
In Train20 the button should move the serial number from H across to the log column.
Need to sets of logs. One on each train or location sheet and one on the logregister50.

1.At the specified the train departs and comes to the first location where it leaves(removes) a freight car from the train and leaves at the location or picks up a freight car from that location and is added to the train list.

The timetable is checked for next movement in time order.
That movement - the next train (could be same train or another train)does the same as 1. above


etc etc



Thankyou for your assistance
Charles Harris
New Zealand
 

Attachments

vletm

Excel Ninja
sswcharlie1
Yes ...
I didn't get Your bright idea - sorry.
BUT
I tried to do something same, what You have written...
BUT 'my way'!
eg no need so many sheets ...
Basic, You can do You changes in Timetable-sheet ...
You can add/remove 'freight' max 8 per train
You can change 'location' after above
You can ...hmm? ... change 'train' too.
>> all those changes would record two line to LogRegister-sheet.

Timetable-sheet
Y&X-columns are for dropdowns
as well as
AB..AD-columsn are dropdowns for freights
( and same time could see those current locations too )
I didn't test all [im]possible variations, but some parts seems to work already.

Note: If You change Train, Location or Freight- text to other - You'll get 'some errors'.
= I didn't add any error checking ... so far.

I hope that - this sample - is not too far from You idea.
...hmm? actually, almost opposite side of the World
 

Attachments

sswcharlie1

New Member
Hi vletm

Wow, thanks for the new WB. It is looking good. Doing it your way is great. I am printing off the vba tomorrow so I can read thru and see how it works. I need to learn about some of the terminology. eg ASE and Case to see what they do.
I will get back to you in a day or two.
Where do I move a serial number from one Freight to another Freight by using the event macro ?
Thankyou for your great work.

Charles Harris
 

vletm

Excel Ninja
sswcharlie1
That was a sample and of course, there should do some modifications.
I did only some 'steps'.
You wonder term ASE ...hmm? It's like Application-ScreenUpdating-EnableEvents -switch ON/OFF.
as well as Case ...hmm? Instead of many if - there could be many cases as I've written ...
Your Where do I move a serial number from one Freight to another Freight by using the event macro ?
That 'moving' ... case 11 to 18
1st in Worksheet_SelectionChange, the code saves previous value and finds out possible 'Freight's for dropdown
2nd in Worksheet_Change after change value, the code updates AB-column as needed and so on... as well as add rows to Log.

As I wrote - If You would like to use 'more realistic train-, location- & freight-terms - it's possible!
1st You should 'move' all trains & freight to same location and after that modify those AB:AD-terms as needed.
It would give 'errors' ... because ... there are still 'old-terms' in 'Timetable' - I did not add any error checking... not yet.
You can ask anytime ... if You need some help.


Ps that the last part of code Sub xxxx(),
is there ... if nothing seems to work ... then run that code manually!
It activate EnableEvents.
 

sswcharlie1

New Member
Hi vletm
Gettingk to know the code now. Some questions before we go to next step.

Macro 1 This macro activated only when a value deleted or added - how does it know the complete data range to be looked at. In timetable register. Say range I:R but there are other columns in use further across spreadsheet. ?
'With Active Sheet' is this 'Timetable' sheet ?
"This macro finds the previous value" in your notes. Which part of code 'finds ' the prev value ?
What is the difference between: h and "H"
: .Cells(h, "J")=Sheets("Timetable").Cells(1 ,"J") What does the 1 do.

I am happy to go to next step. Looking great.

Thanks
Charles
 

vletm

Excel Ninja
sswcharlie1
Questions are welcome ...
> Macro 1 ...
There are two macros; one (lower) will activate then You'll select any cell and other (higher), then You'll change its value.
Those will work as cell-by-cell and within range (there have to be something in train-column).
There are also Select case ax, which checks columns.
...hmm? If You have need to spread it over R-column, then those Y...AD-columns should move 'somewhere' (needs coding too!).
That my file was a basic sample, based Your idea.

> I use there Activesheet because it basically use only one sheet ... those can change to Sheets("Timetable).

> "This macro finds ..
1st in Worksheet_SelectionChange, the code saves previous value and finds out possible 'Freight's for dropdown
You select any valid cell (from I...R-column and below of #10 row), there are codes .cells(1,ax) = .cells(ay,ax), which save selected cells values to row 1.
Next, if You change cells value, then there is already 'saved' previous value for updating values. Need to know both values.

> What is the difference between: h and "H"
h is used with row number
"H" is column-letter; there are fixed columns and for me, I can see at once ... where!

> What does the 1 do; eg .Cells(1 ,"J")
It is the 1st row in that column and those values are just those previous values (saved while selecting cell).

> Next step >
You should have some kind of master plan, what there should be?
eg any layout changes needs modifications with coding.
... as well as ...
eg case if there are three freights and You would take 1st or 2nd away, then all or last two freights should/could take away same time.
It would be a challenge to do in the real World, as now with this sample.
 

sswcharlie1

New Member
Hi vletm
Have attached a new smaller workbook. The event macro is partly working, I will change some column references etc to fix.
Only 4 changes to keep it small and simple. I am not sure how the "1Freight" columns work. I think these should be listing the wagons/carriages in each location at the end of each step.
The LogRegister nearly there.
Looking go0od so far. Your comments would be appreciated.
Charles
 

Attachments

vletm

Excel Ninja
sswcharlie1
Ouch!
That why partly working is because You seems to clear AB:AD-columns data!
Those shows all items which You can use!
= All Trains, all Freight & those current locations!
( Which 'names' of trains? How do You want to call those 'Freight's? You seems to use 'numbers'.
List ALL Your needed numbers. ... as well as locations. )
... and that You could use those eg only once - one freight can be only in one place!


What is meaning on 'Arrival Time'?
Later ... times comes from You actual time, then You'll do some changes!
OR
Do those eg by press the button - and then next 'Timetable' row would do like an animation?

You're using term 'delete' then You're removing eg 1487 from train ... hmm?
My version 'moved' that removed 1487 to 'somewhere' ... and ... later, that could use again.

I try to do something later ... then I have figured again ... what do You would like to have?
as well as 'correct' terms ... items ... what to use?
 

sswcharlie1

New Member
Hi vietm

Had the wb working as per your instructions. Good. The row data moved across. (the cell references for Test for Location2 R1 had to be changed to work. R13 etc
Nothing showing in the LogRegister yet.

What does the R1-R4 do at each location do?
Thankyou
Charles
 

vletm

Excel Ninja
sswcharlie1
...hmm?
There are four Locations (1..4) and each has four Rails (1..4) ( now, with that text)
Those are FIXED number of items! ( = do not modify! )

As I tried to explain eg:
1) You select any 'Train' ( = green)
2) You can see its current position Left/Top-corner
3) You can change 'Train's location with cell [G9] and its Rail with cell [H9]
4) After that You can 'MOVE' it by press MOVE ( cell [L9] )

There are few more things which can do:
Freights (yellow) can release / connect by select it. If Freight is connected, its font is bolded.
Released freights do not 'move' alone. Only with Train can move Freights :)

Still, I gotta figure 'few' options .. as You would those missing things which cannot do so far.

Now, LogRegister works ... someway.
 

Attachments

sswcharlie1

New Member
Hi vletm

It is looking very good. Now to see the movements of Freight's. No hurry just when you can sort.
Regards
Charles
 

vletm

Excel Ninja
sswcharlie1
Here more functionality ...
One note - almost every time have to select BOTH (location and rail).
Try to test all 'buttons'.
There could be some unwanted features ... still.
= I didn't test everything!
Ps You can change names (texts) of trains as well as freights ( BUT do not change colors of those! )
 

Attachments

sswcharlie1

New Member
Hi vletm

Trying to change texts etc but if I select a say Freight 4 the cell data moves up about 3 rows and will not allow to change to say number 4578
.
When need to select both location and rail do I use the normal shift key and arrow to select ?

I can then make some text changes.

Thanks
Charles
 

vletm

Excel Ninja
sswcharlie1
Change texts:

Trains ... Yes, those are possible to change - at once.
Freights ...hmm? I should create option for that. It's possible, but ...
>>> I tested to add [ RENAME ]-button for 'Freights' <<<

Select both...
I mean that something like next...
Select eg Train 8 by mouse
> Top row will show its Location & Rail as well as selected Train - okay
If You want to 'move' Train 8 to Location 2 and Rail 2 ...
then select from left top dropdown Location 2 and
after that from next cell's (right side) dropdown Rail 2
>> now, both (Location and Rail) has selected >>
Next You can press [ MOVE ] to ... move that train with connected freight to Location 2/Rail 2
[ MOVE ] ... moves always from the beginning or to then end of rail.
( [ PULL ] no need both selections )
Note: There could be possible to do accidents too - so be careful - if tries to move trains in wrong positions.
 

Attachments

Last edited:

sswcharlie1

New Member
Hi vletm
Everything working for me a lot better. I use a laptop and was using the touch pad. I have today installed a bluetooth mouse, and it made it so much easier to get around Excel than before.
Have attached a WB. I have moved trains around good. But if you want to move the train a second time it does not move the train to new position, it does however log some of the information, not all. See Log.
Tried changing text of freights etc but not change.
On the sheet ' Description ' there is a time say 9:10 etc could this be included in the log somehow? Not urgent.Each time train moves there would be a time logged.
Meantime can we be able to change freights text ?
Thanks
Charles
 

Attachments

sswcharlie1

New Member
Hi again
Later on we ne3ed to be able to show the 'freights' currently at each location. Same or similar as' freights ' . A dropdown maybe for each location ?
Then select a freight to be moved from location onto the train# list.
Thks
Charles
 

vletm

Excel Ninja
sswcharlie1
Train1 & 2 were 'released' as well as 'backward' if compare with freights ...hmm?
If text is bolded then it is connected ... if it is connected then it can 'move'... with freight.
If 'direction's are different (train <> freight) then 'no work' ... like sensitive?
After any 'move' or so ... there would need always select both Location and Rail for Trains.
( If 'left-top-cells' are empty ... then 'Train' do not know - where to go? )

Changing texts ...
You're using older version .. test with my newer version which I have loaded ( just before You were busy ).

'Description'-sheet
... then You should update those Trains - Freights same time as You 'Description'-sheet has information.
eg 09:10 ... You should do it before 09:10 (Your local real time ).

Later on we ...
Do You mean that Freights should move from Location/Rail to other Location/Rail without Train?
...hmm? ... then ... why needs those Trains?
 

sswcharlie1

New Member
Hi vletm
Have downloaded the correct WB . Yes the changing of text works good. Have put serial numbers there now.
'Rename' is this working yet to rename a location or train description. Say from 'Location1' to 'Atlanta' or 'Train1' to 'GVWA-E ' or similar.

Yes, there must always be a train. If say train has 6 freights and arrives at location1 and there are already 4 freights at location1, from the train they leave 1 more freight at location1, that is the only change at this location. Train1 will now have only 5 freights and location1 will now have 5 freights.

The list of freight on each train is good, need something the same for freights at each location. We can then see all freight and if they are at a location or on a train.

Still working out how to use Forward/Backward and Direction.
How is release used.
Thnkyou
Charles
 

Attachments

vletm

Excel Ninja
sswcharlie1
Location text can rename as normal cells (select that cell and modify as needed),
BUT there have to be those four same location texts in one 'grp' as now!
= One Station will be ... one station ... and ... its rails will be same position all the time!
( Do not change Rail-text at all! )

Each Location has four Rails which can use (with some rules). eg 'Move' can do only to empty Rail.
Each Location can have 28 Freights (7 per each Rail).

Even now, You can see where (in which Location/Rail - those do NOT move anywhere) is Your named Freight or Train.

[ RELEASE ] If Train or Freight -text are bolded then those are connected to next item.
For/Backward - select Train - click For/Backward - Train would move wanted direction (do not make accidents).
Direction - Notice Train/Freight text align - if text is left side or right side - that matters (especially with above feature).
'my Trains' can only pull Freights (as with many cars - there are 'connection' only that side)
[ PULL ]-option - means moving left/right end of rail ... like to Station.
 
Top