Malli-makro: luo riville merkintä, kun taulukkoa päivitetään

Kun yhdellä Excel-tiedostolla on monta käyttäjää, on usein mielekästä pitää kirjaa siitä, kuka on tehnyt minkäkin päivityksen taulukkoon. Päivitysajankohdan kirjaaminen käsin on tarpeetonta, sillä tietojen ylläpidon voi näppärästi automatisoida.

Automaatio saadaan aikaan tässä artikkelissa esiteltävällä pienellä VBA-makrolla, joka on helposti muokattavissa lähes mihin tahansa muotoiltuun taulukkoon sopivaksi. Tässä artikkelissa ei opeteta VBA-ohjelmointia, vaan kerrotaan, miten tämä valmis makro liitetään osaksi olemassaolevaa tiedostoa ja mitkä kolme makrossa olevaa sanaa tulee vaihtaa omaan taulukkoon sopivaksi.

 

Kuva: Esimerkkimakron sisältö Esimerkkimakron sisältö. Klikkaa kuva suuremmaksi.

TÄRKEÄÄ: Käsiteltävästä tiedostosta tulee tehdä varmuuskopio ennen makron liittämistä siihen!

Kun varmuuskopio on tehty, makro kannattaa kopioida sellaisenaan käsiteltävään tiedostoon nuolia seuraamalla:

  • Kuvassa sivua Päivitysmakro klikataan hiiren oikealla napilla ja valitaan kohta Näytä koodi.
  • VBA-ikkunassa kaksoisklikataan haluttua sivua. Kuvassa Taul1(Päivitysmakro).
  • Ladataan makron sisältävä paketti (.zip, 1kt), avataan siinä oleva tekstitiedosto ja kopioidaan sen sisältö kokonaan.
  • Liitetään makro kuvan osoittamaan kohtaan VBA-ikkunassa.

Sen jälkeen makrosta tulee muuttaa kolme sanaa, jotta se toimii oikein. Jokainen näistä on kommentoitu merkinnällä MUUTA TÄTÄ:

  • taulukko: vaihdetaan lainausmerkkien sisään sen taulukon nimi, jonka muutoksia makro tarkastelee.
  • muutosaika: vaihdetaan lainausmerkkien sisään sen sarakkeen otsikko, johon makro kirjaa muutosajankohdan.
    • Kuvassa violettitaustainen Muutosaika.
  • paivittaja: vaihdetaan lainausmerkkien sisään sen sarakkeen otsikko, johon makro kirjaa päivityksen tehneen tahon.
    • Kuvassa violettitaustainen Päivittäjä.

Huomioitavaa:

  • Kun makro on valmis, sen toiminta tulee testata muuttamalla jotain taulukon arvoa.
  • Mikäli makro ei toimi oikein, tarkistetaan, että taulukon nimi ja muutostietojen sarakeotsikot on kirjoitettu TÄSMÄLLEEN siinä kirjoitusasussa, joissa ne ovat kohdetiedostossa.
  • Muutostietoja sisältävät sarakkeet voivat sijaita muuallakin kuin kahdessa viimeisessä sarakkeessa.
  • Jotta makro toimii, tulee sallia makrot tiedostoa avatessa painamalla Suojausvaroituksen nappia Ota sisältö käyttöön.
  • Makron sisältävää taulukkoa voi suodattaa ja lajitella vapaasti.
  • Makron sisältävään taulukkoon voi vapaasti lisätä uusia sarakkeita ja rivejä sekä poistaa olemassaolevia rivejä ja sarakkeita.
  • Tiedosto tulee tallentaa .xlsm -muotoon:

Latauslinkit:

 


Caduseus Consulting avustaa ja kouluttaa monenlaisissa IT-tarpeissa!

Mikäli Excel-työ takkuilee ja haluatte ammattilaisapua jonkin projektin kanssa, voitte tilata asiantuntijapalvelua hintaan 45 €/h (+ alv). Kaikista toimeksiannoista voidaan antaa myös kiinteähintainen kokonaistarjous.

Katso myös hintalaskuri edullisille tilauskoulutuksille ja tilaa organisaatiollenne suosittu Excel helpoksi työelämässä-koulutus.


 
 

90 kommenttia.

  1. Royal CBD

    elokuu 4, 2020 at 03:09

    Hi to all, how is the whole thing, I think every one is getting more from this web site,
    and your views are fastidious designed for new visitors.

    my blog – Royal CBD

    Reply
  2. xbox codes generator

    elokuu 19, 2020 at 01:09

    Magnificent beat ! I would like to apprentice while you amend your web site,
    how could i subscribe for a weblog website? The account aided me
    a applicable deal. I had been a little bit acquainted of
    this your broadcast provided vivid transparent concept

    Reply
  3. scule pescuit

    elokuu 23, 2020 at 22:55

    Hi there everyone, it’s my first go to see at this
    website, and article is in fact fruitful for me, keep up posting such articles or reviews.

    Reply
  4. free vbucks

    elokuu 25, 2020 at 03:45

    Hi, this weekend is pleasant in support of me, since this point in time i am reading this enormous informative piece of
    writing here at my house.

    Reply
  5. v bucks free generator

    elokuu 25, 2020 at 04:06

    I know this web site gives quality depending content and other stuff, is there any other web site which offers these kinds
    of information in quality?

    Reply
  6. free the vbucks

    elokuu 25, 2020 at 04:39

    This web site certainly has all of the information I needed about this subject
    and didn’t know who to ask.

    Reply
  7. free vbucks generator

    elokuu 25, 2020 at 04:44

    Howdy this is somewhat of off topic but I was wondering if blogs use WYSIWYG
    editors or if you have to manually code with HTML. I’m starting a blog soon but have no coding
    know-how so I wanted to get advice from someone with experience.
    Any help would be enormously appreciated!

    Reply
  8. v bucks free

    elokuu 25, 2020 at 05:11

    If you want to obtain a great deal from this paragraph
    then you have to apply such strategies to your won web site.

    Reply
  9. free vbucks

    elokuu 25, 2020 at 06:10

    Very nice article, exactly what I needed.

    Reply
  10. free the vbucks

    elokuu 25, 2020 at 06:38

    Your style is really unique in comparison to other folks I have
    read stuff from. I appreciate you for posting when you’ve got the opportunity, Guess I will just
    book mark this site.

    Reply
  11. Merle

    elokuu 25, 2020 at 08:22

    I am regular visitor, how are you everybody?

    This post posted at this web site is actually nice.

    Here is my web-site – Merle

    Reply
  12. see my site

    elokuu 27, 2020 at 10:49

    Excellent post. Keep posting such kind of info on your page.
    Im really impressed by it.
    Hi there, You have performed an excellent job. I’ll certainly digg
    it and personally recommend to my friends. I am sure they’ll be benefited from this web site.

    Reply
  13. how to cheat on pokemon go

    elokuu 29, 2020 at 12:38

    This is my first time go to see at here and i am truly
    pleassant to read everthing at single place.

    Reply
  14. hack brawl stars

    elokuu 31, 2020 at 20:15

    Ridiculous quest there. What occurred after? Thanks!

    Reply
  15. psn codes generator

    elokuu 31, 2020 at 20:40

    Hi there, I want to subscribe for this website to obtain most up-to-date updates, so where
    can i do it please help.

    Reply
  16. get free robux fast

    elokuu 31, 2020 at 22:25

    Just desire to say your article is as surprising.
    The clarity in your post is simply spectacular and i can assume
    you’re an expert on this subject. Fine with your permission allow me
    to grab your RSS feed to keep up to date with forthcoming post.
    Thanks a million and please keep up the enjoyable work.

    Reply
  17. coin master hack

    elokuu 31, 2020 at 23:02

    Hi to every one, as I am truly eager of reading this weblog’s post to be updated on a regular basis.
    It consists of good data.

    Reply
  18. trucos para coin master

    syyskuu 6, 2020 at 20:32

    naturally like your web-site however you have to take a look at the spelling on several of your
    posts. Several of them are rife with spelling issues and I find it very troublesome to tell the
    reality on the other hand I will certainly come back again.

    Reply
  19. avast driver updater license key

    syyskuu 6, 2020 at 20:50

    This is very interesting, You’re a very skilled blogger.
    I have joined your feed and look forward to seeking more of your
    excellent post. Also, I’ve shared your web site in my social networks!

    Reply
  20. avast activation code

    syyskuu 6, 2020 at 21:59

    Heya i am for the first time here. I came across this board and I find It truly useful & it helped
    me out a lot. I hope to give something back and aid others like you helped me.

    Reply
  21. avast internet security keygen

    syyskuu 6, 2020 at 23:37

    Every weekend i used to go to see this site, as i wish for enjoyment, as this
    this site conations truly fastidious funny stuff too.

    Reply
  22. coin master mod apk

    syyskuu 7, 2020 at 13:20

    This design is spectacular! You definitely know how to keep a reader amused.
    Between your wit and your videos, I was almost moved to start my own blog (well, almost…HaHa!) Wonderful
    job. I really loved what you had to say, and more
    than that, how you presented it. Too cool!

    Reply
  23. robux generator

    syyskuu 9, 2020 at 02:59

    This is very interesting, You’re a very skilled blogger.
    I’ve joined your feed and sit up for seeking more of your great post.

    Also, I’ve shared your site in my social networks

    Reply
  24. free spins coin master

    syyskuu 11, 2020 at 01:36

    Hey very interesting blog!

    Reply
  25. coin master free spins

    syyskuu 11, 2020 at 02:24

    Very nice article, exactly what I needed.

    Reply
  26. coin master free spins

    syyskuu 11, 2020 at 02:37

    Very nice article, exactly what I was looking for.

    Reply
  27. free coin master spins

    syyskuu 11, 2020 at 02:57

    I got this web site from my friend who shared with me about this website and now this time I am visiting this web site and reading very informative articles
    or reviews at this time.

    Reply
  28. free spins coin master

    syyskuu 11, 2020 at 03:16

    Hi, I do believe this is a great website.
    I stumbledupon it 😉 I’m going to revisit yet again since I bookmarked
    it. Money and freedom is the greatest way to change,
    may you be rich and continue to guide others.

    Reply
  29. free coin master spins

    syyskuu 11, 2020 at 03:17

    Hi, this weekend is pleasant in favor of me,
    because this time i am reading this impressive educational
    paragraph here at my house.

    Reply
  30. coin master free spins

    syyskuu 11, 2020 at 04:11

    hello!,I really like your writing so so much!
    percentage we be in contact extra about your post on AOL?
    I require an expert on this space to unravel my problem.
    Maybe that’s you! Taking a look forward to peer you.

    Reply
  31. free coin master spins

    syyskuu 11, 2020 at 04:26

    I know this website gives quality depending articles and
    additional material, is there any other web page which gives these stuff in quality?

    Reply
  32. free coinmaster spins

    syyskuu 11, 2020 at 05:33

    I read this paragraph fully on the topic of the difference of latest and earlier technologies, it’s remarkable article.

    Reply
  33. free coinmaster spins

    syyskuu 11, 2020 at 06:08

    I do consider all of the ideas you’ve presented to your post.

    They’re really convincing and can certainly work. Nonetheless, the posts are too brief for beginners.
    May you please lengthen them a bit from subsequent time?
    Thanks for the post.

    Reply
  34. free coinmaster spins

    syyskuu 11, 2020 at 06:39

    wonderful issues altogether, you just won a new reader.
    What may you suggest in regards to your post that you simply made some days in the past?

    Any certain?

    Reply
  35. free coinmaster spins

    syyskuu 11, 2020 at 08:04

    Thank you, I have recently been searching for info about this topic for ages and yours is the greatest I have came upon so far.
    However, what concerning the bottom line?

    Are you positive concerning the supply?

    Reply
  36. free spins coin master

    syyskuu 11, 2020 at 14:57

    Having read this I thought it was extremely informative.

    I appreciate you finding the time and energy to put this content together.
    I once again find myself spending way too much time both reading and leaving comments.
    But so what, it was still worthwhile!

    Reply
  37. coin master free spins

    syyskuu 11, 2020 at 16:50

    I’m not sure exactly why but this site is loading incredibly
    slow for me. Is anyone else having this issue
    or is it a problem on my end? I’ll check back later
    on and see if the problem still exists.

    Reply
  38. free spins coin master

    syyskuu 11, 2020 at 19:53

    Wonderful post! We will be linking to this great
    post on our site. Keep up the good writing.

    Reply
  39. https://tabbles.net/users/dominicallen

    syyskuu 15, 2020 at 10:13

    My family members all the time say that I am killing my time
    here at net, but I know I am getting experience every day by reading such good
    content.

    Also visit my webpage blog (https://tabbles.net/users/dominicallen)

    Reply
  40. bingo blitz free credits

    syyskuu 15, 2020 at 17:37

    I just couldn’t leave your website before suggesting
    that I really enjoyed the usual information a person supply for your guests?

    Is gonna be back steadily to investigate cross-check new
    posts

    Reply
  41. free robux generator

    syyskuu 16, 2020 at 09:10

    Hey! This is kind of off topic but I need some guidance
    from an established blog. Is it very hard to set up your own blog?
    I’m not very techincal but I can figure things
    out pretty fast. I’m thinking about creating my own but I’m not sure where to begin. Do you have any tips or suggestions?
    Many thanks

    Reply
  42. free v bucks

    syyskuu 16, 2020 at 10:03

    This site certainly has all of the information I wanted about this subject and didn’t
    know who to ask.

    Reply
  43. mike tyson vs roy jones jr livestream full

    syyskuu 17, 2020 at 16:44

    Attractive section of content. I just stumbled upon your site and in accession capital to claim that
    I get actually loved account your weblog posts. Any way I’ll
    be subscribing for your augment or even I fulfillment you
    access constantly rapidly.

    Reply
  44. robux generator

    syyskuu 19, 2020 at 01:41

    hello there and thank you for your information – I’ve definitely picked
    up anything new from right here. I did however expertise
    a few technical points using this website, as I experienced to reload
    the site a lot of times previous to I could get it to load correctly.
    I had been wondering if your web hosting is OK? Not that I am complaining, but
    sluggish loading instances times will sometimes affect your placement in google and could damage your high-quality score if advertising and marketing
    with Adwords. Anyway I’m adding this RSS to my e-mail
    and can look out for much more of your respective exciting content.
    Ensure that you update this again very soon.

    Reply
  45. free robux

    syyskuu 22, 2020 at 08:31

    Howdy! This post could not be written much better!
    Looking at this post reminds me of my previous roommate!
    He always kept talking about this. I am going to send
    this post to him. Pretty sure he’s going to have a good read.
    Many thanks for sharing!

    Reply
  46. mike tyson vs roy jones jr livestream

    syyskuu 23, 2020 at 06:28

    Very soon this web page will be famous amid all blog viewers, due to it’s good posts

    Reply
  47. Nagy Cucure

    syyskuu 23, 2020 at 23:08

    This is a pure nice post. Thankyou pro posting this remarkable article.

    xbody

    Reply
  48. free netflix account generator

    syyskuu 25, 2020 at 02:31

    Hi! This is my first visit to your blog! We are a group of volunteers and starting a new initiative in a community in the same niche.
    Your blog provided us valuable information to work on. You have done a wonderful job!

    Reply
  49. free steam wallet codes generator

    syyskuu 25, 2020 at 04:23

    Remarkable issues here. I’m very glad to peer your
    post. Thank you so much and I’m taking a look ahead
    to contact you. Will you kindly drop me a e-mail?

    Reply

Vastaa

Sähköpostiosoitettasi ei julkaista. Pakolliset kentät on merkitty *