Excel VBA CalendarControl advice

[PeteSmith]

I have a userform with a calendar control (date), a text box (notes), and a command button (submit). The user selects a date, enters some notes and then clicks the submit button, the data is then saved to the next empty row in the worksheet.

I only want the user to enter one set of notes for any given date (to avoid duplicates) how can I get the notes to update based on the calendar control. For example, the user selects the 01/01/10 from the calendar control, there are already notes saved for this date previously, the notes text box then displays these notes, instead of simply adding a new ‘record’.

I’m not sure on the best way to approach this, I have considered using advanced filter, vlookup, neither seem very efficient - any pointers on how i should approach this?

[petrefax]

erm....my first thoughts would be, try using the DTPicker control instead of the full calendar, you may have a bit more luck using

dtpicker1.value =  <cycle through column & pick up values>

personally i find the DTPicker a lot easier to work with than the calendar control. just personal preference i guess.

either that or, if your dates column contains no duplicates you could try setting the dtpicker.change event to create a variable based on dtpicker.value search the column for this, once found its a simple task of offsetting from the selected cell (the one with your date) to populate the notes textbox. you'd probably have to throw it into an IF statement to do one thing if the value already exists & another if it doesn't

these are just a few random ideas...sorry buddy but i've a bit too much work on the go to actually test them out at the minute, but will try them out as soon as i get chance cos i'm interested to see if they work...thais would make a good addition to my code library

[scaryjim]

You could try not using a spreadsheet as a database

Sorry, not helpful I know, but storing information for later retrieval is the job of a database: spreadsheets are designed for making repeated calculations on sets of data. The problem is a lot of people characterise Excel as using "tables" (which it doesn't) and try to use it like a database.

So, my first tip is to see if you can migrate this to a really simple Access db. You'd only need one table, with 2 columns (date and comment).

I've not used the CalendarControl, but I assume since it's a standard MS control it'll have an OnChange action; you'll need to add some code to that, telling it to look up the date and retrieve the comment if there is one already. I believe in Excel VBA you need to do that by setting up a Range object to represent the cells in the spreadsheet, then searching through that, possibly row by row (although, again, I've not done this in Excel so I could be wrong there). In Access, you'd simply use a query to find the right comment. In fact, in Access you could bind the Form to the table and use the OnChange to reset a filter. There's lots of fairly easy yet powerful ways to do this in Access, so if you can migrate it I really would strongly recommend it...

[petrefax]

in an ideal world yes, access would be better, but if this is a work-based thing then economics may mean Excel is the only way forward. Excel comes as part of MS offcie standard, access doesn't, so access incurs an additional per user licensing fee

put simply, i've previously been told by those who hold the purse strings to do stuff in excel & not access!

(suits me tho, i hate access, its one of the crappiest pieces of software known to man if you ask me)

cycling through all entries in a column is straightforward enough, code as below. this assumes you have a header row so want to start in cell A2. if you want to start anywhere else just change the "A2" reference and the lngRow = 2 to whatever cell / row you want

Private Sub CycleThroughColumn()
     
    
    Dim lngRow As Long
    
    
    lngRow = 2

    Range("a2").Select

    Application.ScreenUpdating = False

    Do While ActiveSheet.Cells(lngRow, 1) <> ""
        
        '<your code goes here>    
        
        lngRow = lngRow + 1
        
    Loop
    
    Application.ScreenUpdating = True    
 
End Sub

[scaryjim]

... economics may mean Excel is the only way ... i've previously been told by those who hold the purse strings to do stuff in excel & not access!

(suits me tho, i hate access, its one of the crappiest pieces of software known to man if you ask me) ...

my first tip is to see if you can migrate this to a really simple Access db

I'm aware of that, which is why I specifically said "see if" you can migrate it, not just "migrate it", and also tried to give some Excel advice too In my experience businesses that have invested in MS Office often go for the Professional version, which *does* include Access, but on the other hand I once had to write an entire data entry and management tool in Word, because the client's IT department wouldn't even give them Excel...

One of the problems with Access, and the main reason so many people seem to dislike it, is that it's very easy for an inexperienced user to write a god-awful damn mess of a data application in Access. But if you're a half decent devleoper, the fact that it is a complete and tightly integrated database application development environment makes it a brilliant tool for quick jobs like this. So when these questions come up I will always advise people to migrate them to Access if it's available.

[petrefax]

but on the other hand I once had to write an entire data entry and management tool in Word, because the client's IT department wouldn't even give them Excel...

ouch, that must've been a world of fun, bravo for managing to do that

it's very easy for an inexperienced user to write a god-awful damn mess of a data application in Access

i think this is mostly where my hatred of it comes from. having to unravel & make good some hideously over-complicated messes of databases in the past.