Posts by ambro

I would like to create a macro that plays a sound every 45 seconds or so.
In other words, I would like to have a countdown timer that counts from 45 seconds down to 0 seconds and at the end it plays a sound file(could be mp3, wav, etc) to signal the end. After that it should automatically reset itself back to the start of 45s and count down again. This would be repeated about ten to eleven times.

If possible a visual figure of the actual numbers counting down would also help!

What is the easiest way of doing this? It's too confusing.
Lets say the audio file was located in:

C:\my sounds\beep.wav

Thanks!

Re: Find Date & Pass Offset Cell To Variable

How can I set the systems' date (function Date) to the 1st of whatever month it is, that is, if I am running the macro today (21/10/07) the system date would equal 01/10/07?

This way I could set todaysmonth variable to system date and then search for it in the spreadsheet.

thanks.

Re: Find Date & Pass Offset Cell To Variable

The master value is different for every month.
Basically, I will run this macro every month and I want to find the mastervalue for today's month, when I am running it. The dates are in the true excel format (the way they appear in the formula bar) as dd/mm/yyyy but I have formated the cells to display them as mmm-yy.

It is not feasible to change the dates' format at this point anymore. I want my macro to find the correct row where the date equals today's month, that is, if today was 21 September 2007, I want the macro to go the row where the date is Sep-07 and return the value for the cell next to it (offset 0,1).

I have tried some of the code above, but as expected I get the error "run time error '91': Object variable or With block variable not set."

The trouble I am having as in my code I am looking for today's date, but in the spreadsheet
the date is set to the first of each month and only displaying mmm-yy. So they don't match.

What's the easiest way of achieving this?

Thanks,

Ambro

Re: Cell Value To A Variable

The dates are already in the column and are in the format mmm-yy.
I want vba to find the cell that is the equivalent to today's month and get the offset value as alreayd pointed out.

todaysmonth = date
Set monthcolumn = Range("I1", "I1000").Find(todaysmonth, LookIn:=xlValue)
MASTERvalue1 = monthcolumn.Offset(1, 0).Value

This is the code I have so far, but monthcolumn is empty because it doesn't find the correct date in the column.
I want to set todaysmonth to the mmm-yy format, and maybe that would work?

How do I use VBA to send the value of a cell, for example, cell J77 to the variable "mastervalue1"?

I have a spreadsheet that has a column with dates ( in the format "mmm-yy") and next to it a column that has values. I want to pass the value of the cell that is next to the date cell that contains today's month to a variable in VBA.[hr]*[/hr] Auto Merged Post;[dl]*[/dl]here's an example of my spreadsheet

Hello.

I am new with using VBA in Access and don't really know much about it!
I have seen in the past someone use VBA in Excel to automatically email spreadsheets as attachments.

Is it possible to the same with printed Access reports?

Thanks,

Ambro

Re: Set Focus To A Cell

Thanks very much for the quick reply!
The SendKeys is what I was looking for! Very helpful!
You're right, it would be better to do the validation using the calendar but I am not sure how to do that!
I got it to work now, though. thanks.

Hello,

I have a macro that runs a calendar and enters the selected date on the calendar onto a cell.
You can choose a Start Date and an End Date separately using the calendar, that is, you click on the Start Date cell and run the calendar to select a date then you click on the End Date cell and run the calendar to select a date.

I would like to have some form of validation now to prevent users from entering an End Date that is earlier than a Start Date.

I have tried the built in Validation rules in Excel but that only works if you manually type the dates in each cell and it doesnt work if you use the Calendar macro to enter the dates.

If you use the calendar for the End Date and then double-click it (to edit its contents) then it works! How do I make VBA make that cell editable, that is, how do I make the validation work?

Also, what is the code to make the activecell ready to be typed in, i.e. the cursor is flashing in that cell ready to type?

I hope this makes sense. Can anybody help, please?

Thanks,

Ambro

thanks johnccc!
but it turned out that if i use :

Application.Dialogs(xlDialogPrinterSetup).Show

is actually more effective to me in this case. This way the user can specify a printer himself.
Thanks anw mate!

:bouncy: :cheers:

Hello there. Its a simple question i suppose. Still i dont know the answer to it! :barf::barf:
Im running a macro to print a sheet in excel. I have everything set up(page breaks, etc) but i want to select my coloured printer as opposed to the B&W one when the macro is running. How can i get it to choose the printer? Cheers,

victor.

oh sorry, my bad. didnt realise i was in the WORD forum and not Excel... sorry!

Hello there. Its a simple question i suppose. Still i dont know the answer to it! :o :redface:
Im running a macro to print a sheet in excel. I have everything set up(page breaks, etc) but i want to select my coloured printer as opposed to the B&W one when the macro is running. How can i get it to choose the printer? Cheers,

victor.

Hi guys, any ideas on how to select the printer before printing tho? Cheers,

victor