Help eith macro and command function

I need help with the procedure required to complete the 
following task. I have a spreadsheet that if data is 
enetered into cell range J6:N6, the user is prompted to 
eneter a date, which will reside in cell Q6.
Any help will be appreciated.
I;ve attemped to use a form, with a command button and a 
tex dox, however with not much success, any help would be 
appreciated.
0
anonymous (74722)
6/27/2004 1:29:35 AM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
180 Views

Similar Articles

[PageSpeed] 10

You could use the Wroksheet_Change event to prompt for the date. For 
example:

'===========================
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row >= 6 Then
   If Target.Column >= 10 And Target.Column <= 14 Then
     Cells(Target.Row, 17).Value = _
       InputBox("Please enter a date", "Date")
   End If
End If
End Sub
'=============================

To add this code to the worksheet:

Right-click on the sheet tab, and choose View Code.
Copy the code, and paste it onto the code module.


Prompt pop-up wrote:
> I need help with the procedure required to complete the 
> following task. I have a spreadsheet that if data is 
> enetered into cell range J6:N6, the user is prompted to 
> eneter a date, which will reside in cell Q6.
> Any help will be appreciated.
> I;ve attemped to use a form, with a command button and a 
> tex dox, however with not much success, any help would be 
> appreciated.


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
6/27/2004 12:44:06 PM
Hi Deb,

Thanks for this response.  I've been trying to teach 
myself some VBA programming and moving along slowly but 
steadily.  I'm now trying to get this to Date/Time (NOW()) 
a cell.  No success yet......but am close, I think...:)

Thanks again for this bit of help,

Don 


>-----Original Message-----
>You could use the Wroksheet_Change event to prompt for 
the date. For 
>example:
>
>'===========================
>Private Sub Worksheet_Change(ByVal Target As Range)
>If Target.Row >= 6 Then
>   If Target.Column >= 10 And Target.Column <= 14 Then
>     Cells(Target.Row, 17).Value = _
>       InputBox("Please enter a date", "Date")
>   End If
>End If
>End Sub
>'=============================
>
>To add this code to the worksheet:
>
>Right-click on the sheet tab, and choose View Code.
>Copy the code, and paste it onto the code module.
>
>
>Prompt pop-up wrote:
>> I need help with the procedure required to complete the 
>> following task. I have a spreadsheet that if data is 
>> enetered into cell range J6:N6, the user is prompted to 
>> eneter a date, which will reside in cell Q6.
>> Any help will be appreciated.
>> I;ve attemped to use a form, with a command button and 
a 
>> tex dox, however with not much success, any help would 
be 
>> appreciated.
>
>
>-- 
>Debra Dalgleish
>Excel FAQ, Tips & Book List
>http://www.contextures.com/tiptech.html
>
>.
>
0
anonymous (74722)
6/27/2004 9:12:43 PM
You can change the code to:

   Cells(Target.Row, 17).Value = Now()

Widen the column, if necessary, to display the date and time

Don wrote:
> Hi Deb,
> 
> Thanks for this response.  I've been trying to teach 
> myself some VBA programming and moving along slowly but 
> steadily.  I'm now trying to get this to Date/Time (NOW()) 
> a cell.  No success yet......but am close, I think...:)
> 
> Thanks again for this bit of help,
> 
> Don 
> 
> 
> 
>>-----Original Message-----
>>You could use the Wroksheet_Change event to prompt for 
> 
> the date. For 
> 
>>example:
>>
>>'===========================
>>Private Sub Worksheet_Change(ByVal Target As Range)
>>If Target.Row >= 6 Then
>>  If Target.Column >= 10 And Target.Column <= 14 Then
>>    Cells(Target.Row, 17).Value = _
>>      InputBox("Please enter a date", "Date")
>>  End If
>>End If
>>End Sub
>>'=============================
>>
>>To add this code to the worksheet:
>>
>>Right-click on the sheet tab, and choose View Code.
>>Copy the code, and paste it onto the code module.
>>
>>
>>Prompt pop-up wrote:
>>
>>>I need help with the procedure required to complete the 
>>>following task. I have a spreadsheet that if data is 
>>>enetered into cell range J6:N6, the user is prompted to 
>>>eneter a date, which will reside in cell Q6.
>>>Any help will be appreciated.
>>>I;ve attemped to use a form, with a command button and 
>>
> a 
> 
>>>tex dox, however with not much success, any help would 
>>
> be 
> 
>>>appreciated.
>>
>>
>>-- 
>>Debra Dalgleish
>>Excel FAQ, Tips & Book List
>>http://www.contextures.com/tiptech.html
>>
>>.
>>
> 


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
6/27/2004 10:03:46 PM
Hi Again Deb,

Many thanks for the help with this function.  Have also 
been involved in another thread concerning this issue and 
between the two of you, I think I've got a handle on it.

Thanks again for the wealth of information both here and 
on your web site.

Have a great day,

Don

>-----Original Message-----
>You can change the code to:
>
>   Cells(Target.Row, 17).Value = Now()
>
>Widen the column, if necessary, to display the date and 
time
>
>Don wrote:
>> Hi Deb,
>> 
>> Thanks for this response.  I've been trying to teach 
>> myself some VBA programming and moving along slowly but 
>> steadily.  I'm now trying to get this to Date/Time (NOW
()) 
>> a cell.  No success yet......but am close, I think...:)
>> 
>> Thanks again for this bit of help,
>> 
>> Don 
>> 
>> 
>> 
>>>-----Original Message-----
>>>You could use the Wroksheet_Change event to prompt for 
>> 
>> the date. For 
>> 
>>>example:
>>>
>>>'===========================
>>>Private Sub Worksheet_Change(ByVal Target As Range)
>>>If Target.Row >= 6 Then
>>>  If Target.Column >= 10 And Target.Column <= 14 Then
>>>    Cells(Target.Row, 17).Value = _
>>>      InputBox("Please enter a date", "Date")
>>>  End If
>>>End If
>>>End Sub
>>>'=============================
>>>
>>>To add this code to the worksheet:
>>>
>>>Right-click on the sheet tab, and choose View Code.
>>>Copy the code, and paste it onto the code module.
>>>
>>>
>>>Prompt pop-up wrote:
>>>
>>>>I need help with the procedure required to complete 
the 
>>>>following task. I have a spreadsheet that if data is 
>>>>enetered into cell range J6:N6, the user is prompted 
to 
>>>>eneter a date, which will reside in cell Q6.
>>>>Any help will be appreciated.
>>>>I;ve attemped to use a form, with a command button and 
>>>
>> a 
>> 
>>>>tex dox, however with not much success, any help would 
>>>
>> be 
>> 
>>>>appreciated.
>>>
>>>
>>>-- 
>>>Debra Dalgleish
>>>Excel FAQ, Tips & Book List
>>>http://www.contextures.com/tiptech.html
>>>
>>>.
>>>
>> 
>
>
>-- 
>Debra Dalgleish
>Excel FAQ, Tips & Book List
>http://www.contextures.com/tiptech.html
>
>.
>
0
anonymous (74722)
6/28/2004 12:38:51 AM
Reply:

Similar Artilces:

Help! Outlook 2000 multiple e-mail accounts.
Hi, I am using Outlook 2000. I have set up five different email accounts and when I click on "Send/Receive" I can see the five accounts listed, but when I click on any one of them, the Inbox does not change, it stays with the Default Inbox. Even the Login window appears and I enter the login info, it still stays with the Default Inbox. At this point I cnanot access other e-mail accounts other than the Default one which appears everytime I open Outlook. Any suggestions/comments/assistances is much appreciated. Thank you. Thomas Do you have Outlook 2000 configured in Internet Mail ...

ATLINE Function
Hello all I have received a spreadsheet with an ATLINE function, which Excel XP is just interpreting as a name, but does anyone know what this formula should be? Many thanks Richard From the net I get the following result which is nearly as confusing as your problem!! It appears to be a non XL comment. Cheers from North Yorkshire John AtCLine() Method AtCLine.doc Returns the line number of the first occurence of a string expression within another string expression without regard to case (upper or lower) http://portal.dfpug.de/dfpug/Kategorien/Sprachen/Visual%20FoxPro/ "richa...

Help: Budget doesn't load bills for entire year
Budget reports only show the bill from the month it was created August and forward. Since I just upgraded from Quicken, all of the bills were paid before this month. How can I make money realize that the budget should include these as well. ...

Macros
I'm getting confused. I built two macros for a given spreadsheet and inserted them on a custom toolbar. When we copy the spreadsheet, giving it a new name, and then open the copy and run the macros - excel opens a new spreadsheet with the name of the original. I'm guessing that the macros are assigned specifically to a particular spreadsheet somehow? How do you suggest I work around this? I'd like to copy the spreadsheet and have the macro work in the newly created spreadsheet too. Even if it entails some minor coding changes, thats fine - or is there a way to tell the macr...

PLEASE HELP--ERROR MESSSAGE
I have two error messages. The reason why I am getting this is because I got a new computer and I copied my old outlook information and put it in the new computer. Everytime I open my outlook this message appears and the only thin I have to do is click ok and everything works. But how do I fix it? I did reload outlook serval times and there error message still comes even if I do not put my old information in it. Error 1: "The add-in "C:\Program Files\Microsoft Office\Office\SBCMSYNC.DLL" could not be installed or loaded. This problem may be resolved by using Detect and Repai...

Excel macro list
In Excel 2003 I used to be able to list all macros in a workbook by pressing Alt+F8. Now all I get is a series of ribbon help letters... What's changed? Is there still a way of accessing macros via Alt+F8? Any suggestions appreciated. Hi, ALT+F8 works for me in E2007. What do you mean by 'I get is a series of ribbon help letters' Mike "pbaker6" wrote: > In Excel 2003 I used to be able to list all macros in a workbook by pressing > Alt+F8. Now all I get is a series of ribbon help letters... What's changed? > Is there still a way of...

Office 2001 for Mac help needed!
As an undergrad, I received a copy of Microsoft Office 2001 for Mac and it is installed on my iMac that I used there. However, I just bought a new MacBook for graduate school and need to install Office on that computer now, but when I put the disk in and double click on install it wont work. All of the icons have an x on them and I believe it says it "This application cannot run on this system" or something to that effect. HELP! I need Office asap since classes start this week! Thank you Laura Hi Laura - Your best bet is to hie thee to the university bookstore & get the u...

Passing an argument from a query to a command
I am trying to send a report using an email address in a DB. I wrote the following code for it. Private Sub Reminder_Letter_Command_Click() On Error GoTo Err_Reminder_Letter_Command_Click Dim stDocName As String Dim SendTO As String stDocName = "BG Reminder Letter" RunQuery MsgBox "This is After RunQuery " & SendTO SendTO = [Queries]![BG email only from email from ae code].EmailAddress MsgBox "This is SendTO 2 Value ==== " & SendTO DoCmd.OpenReport stDocName, acViewNormal DoCmd.SendObject acSend...

problems with toolbar-can't access certan commands i.e.hyperlink
can't access some commands - was working fine - now some commands are in grey - the commands disappeared from the pull down in the commands bar. Help magnus wrote: > can't access some commands - was working fine - now some commands are in grey > - the commands disappeared from the pull down in the commands bar. The hyperlink option may only appear when you are creating a Web Publication in certain versions of Publisher. -- Ed Bennett - MVP Microsoft Publisher http://ed.mvps.org Trial version? You may have run out of time. Some pulldowns only work when you are creating a ...

Help on percentages greater than 100
Thanks in advance for your assistance. I have a fairly larger list of numbers that needs to be divided to get a percntage. Due to restrictions, accesses etc some of the numbers which were accurate at the time will result in a number greater that 100%. These values (%) will eventually end up on a graph and should not exceed 100%. A B C 1 88 89 =b1/a1 (101%) 2 88 88 =b2/b2 (100%) the ( ) above are the results. What I need is a way to show the numbers that are > 100% but limit th...

help
Hi, I'm trying to turn the warning message off when I use: Cells.Replace What:="Z Divisional Totals", Replacement:="Divisional Totals", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Second part is: Is there a way to check either the column and rows filled to create border where is pointed to "APPLYBORDER". Your help would be much apprecated. r = Range("A10").End(xlDown).Row ApplyBorder Range("A10", "A" & r) ApplyBorder Range("B10", "B" & r) ApplyBorder Range("C10", "C&qu...

Newbee needs help Combo Box access 2007
I have a access 2000 project that would all me to create a combo box the added the following code , so the values from the combo box would be stored in the current form . also when typing the code below access 2000 would bring up the code string example when typing me.s it would bring up the value "shipper" from the field list is this feature gone or just turned off on my copy Example of code From Access 2000 Private Sub Combo 40_AfterUpdate(cancel As Integer) me.shipper = me.combo40.Column(0) me.Address = me.combo40.Column(1) error This error occurs whe...

Help with passing a string variable into a parameter
hello! here is my problem. I have built a dynamic string called @filepath as char50 i need to place it in: SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', @filePath, 'SELECT * FROM [Sheet1$]') does any one know the correct syntax??? for passing a parameter here OPENROWSET does not accept variables as arguments. You have to use dynamic SQL to build your query (append the @filePath) and execute. -- Plamen Ratchev http://www.SQLStudio.com On Mar 11, 1:25=A0pm, Plamen Ratchev <Pla...@SQLStudio.com> wrote: > OPENROWSET does not accept variab...

Crime Analyst needs help...
I need an Excel macro to find clusters within a list of 6 figure eastin and northing co-ordinates. I can readily export a three column sprea sheet with the column headings: 'crime number' 'easting' 'northing' What I'm after is a Macro to produce additional columns with th headings: 'crimes within 10 metres' 'crimes within 50 metres' 'crimes within 100 metres' 'crimes within 1 kilometre' These can obviously be calculated from the easting and northin columns. I've had a go myself with no success. I've also looked on th web a...

Quartile Function
I would like to use a formula to return which quartile the given data array falls into- that is I would like to return 0,1,2,3,4. 0= min, 1=25%, 2=50%, etc. The quartile function only returns the value of the various quartiles, while I need to know within which quartile the value falls. Any help is appreciated. On Tue, 15 Mar 2005 01:01:02 -0800, "tika528" <tika528@discussions.microsoft.com> wrote: >I would like to use a formula to return which quartile the given data array >falls into- that is I would like to return 0,1,2,3,4. 0= min, 1=25%, 2=50%, >etc. &g...

clear historical quotes
I just upgraded to 2007 trial version (from 2003) and I find what appears to be an undocumented item, so I'm wondering what it does. I'm on the Portfolio Manager screen, and on the left side, I click on Other Tasks->More-> and there's a "Clear historical quotes" menu item. I don't find it documented in the help file, nor in the FAQ, nor on this newsgroup, yet. I'm wondering if anyone's been brave enough to click on it and could tell us what it does? Does it reduce the size of your file? How much? I would hope it does NOT just do a wholesale de...

Plae Help urgent! Recover exchange in a new active directory insta
Hi friends, i nedd your help, i lost my forest and i am trying to recover from backup, but i dont know if this works to active directory knows the exchange, i have to follow some procedure to do that, And if i cant recover my forest y should install a new active directory, and install exchang again, wich is the best way to recover the data from exchange, copy the db's to the exhang database path or what i should do to do that? Thanks a lot for your help Felipx start here: http://support.microsoft.com/default.aspx?scid=kb;en-us;867704 "Felipex" <Felipex@discussions.micr...

Macro to pop up a legend on screen (of sorts)
This is a multi-part message in MIME format. ------=_NextPart_000_0028_01C3EF0C.E78E1400 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Can anyone tell me the best way to present a nice pop-up box with this = information on screen? I don't need anything other than an OK button = that when clicked will disappear. Code Description Equiv. Hours =20 D Days 7.5=20 E Evenings 7.5=20 N Nights 7.5=20 DE Day AND Evening Shift 15=20 EN Evening AND Night Shift 15=20 ND Night AND Day Shift 15=20 ...

Need Help Working with Sub-totals
I have a spreadsheet that I have subtotaled. Now I want to take just those subtotals and play with them: add them, cum them, %'s to total, et c. to do a paredo. If I cut and then paste, I get the whole list. Don't want all the info - just want those subtotals. Is there any way possible to put just them in a new worksheet??? Spent waaaaaaaaaayyyy too much time on this b/w yesterday and today....:( TIA!!!! --- Message posted from http://www.ExcelForum.com/ Hi, if your spreadsheet is on sheet1, you could autosum whatever necessary, then use sheet2 with the subtotals, linking cel...

sumif using the now() function
I am trying to sum numbers using the NOW() function as my criteria, however no luck. sumif($D$1:$BC$1. ">=NOW()", D2:BC2) Any help would be appreciated. Thank you Try this: Assuming you try to sum D2 to BC2 on your example. =if($D$1:$BC$1>=Now(),sum(D2:BC2),"" -- renega ----------------------------------------------------------------------- renegan's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1045 View this thread: http://www.excelforum.com/showthread.php?threadid=52871 renegan: Thanks for your response, however I got #VAL...

CString functions
I want to do the following to strings: 1) Check if first four characters are "DATA" 2) Get the middle 'word' from the following string "DATA 123 xyz" (the middle word is variable length) - extract the "123". 3) Get the last word/number from the following string "DATA SEND 1467436267" What functions should I use to achieve this? I'm new to C++ and finding string manipulation tricky. Gareth wrote: > I want to do the following to strings: > > 1) Check if first four characters are "DATA" > > 2) Get the middle &...

word template ... PLEASE HELP !
Hello, I am trying to insert a word template into a publisher. However all my tries didnt achieve the purpose. I tried to import the document but it didnt take the graphic template only the wording were converted into the publisher. I tried to copy paste the template but i AM not able to do so because i cant even select it through the word document. I tried to take a screen capture and then use paint and to get in only the template then I inserted it into the publisher. It worked but the problem, the qualit of the picture is not good and I need to get this template for my publi...

Calling Addin function?
Hello, I created an addin for excel, work find when in the spreadsheet... but now I want to be able to call my functions from within VBA? The addin is loaded? How can I do this? Thanks, Brian -- Please remove the 123 from the EMAIL Address. This has been added to prevent spamming. pssssst! Here spammer, spammer, spammer. pyramid@ftc.gov, bbroder@ftc.gov, jcheezum@uspis.gov, fraud@uspis.gov Hi Brian! If you set a reference to it you can call it just like the function is in the subject workbook. Tools > References Check against your Addin. -- Regards Norman Harker MVP (Exce...

Macro for my work.....
Can anyone give me some help with a problem I am having in my payroll packet at work - the explanation is quite long and involved, but here goes. My payroll packet has different lettered sheets, one for each letter of the alphabet. I input 2 weeks worth of time in these sheets and I use data validation (data stored in another sheet) to input the names and SS#'s into these sheets for each payroll. I have a "summary sheet" at the end of the workbook that summarizes each entry from the individual sheets (namely Direct Labor,Indirect Labor (Shop), Holiday,Vacation,Total Hours, O.T....

Help with calculations
I have a subform wiwth 3 fieldsDate, Transaction, AmountI would like to sum the amounts by category, for example:Date Transaction Amount1/1/06 Deposit 5001/2/06 Deposit 5001/3/06 Withdrawal 4001/3/06 Withdrawal 2001/7/06 Credit 300Based on this table the results would reflectTransaction AmountDeposit 1000Withdrawal 900Credit 300any help in obtaining the desired result is most appreciated. Oops,Sorry this was posted twice, also withdrawal should reflect 600myxmaster@hotmail.com wrote:> I have a sub...