Posts by RFDisturbance

Re: Macro: Check CheckBox Is True, Current Date For Day/Month, Then Sum TextBox & Cell

Quote from daniel.c

Just to point out that :

Code

DateSerial(2008, 5, 19)

means 5/19/2008
but

Code

DateSerial(2008, 5, 19 - 30)

means 30 days before that date.
Daniel

Display More

Oops, sorry Daniel. Not big on what all this code means, but I think I got it working now. I am including the spreadsheet with the code I used. Let me know what you think, and also to double check my codes I used from Ossah. Thank you too Ossah, you were a big help!!

Re: Macro: Check CheckBox Is True, Current Date For Day/Month, Then Sum TextBox & Cell

Quote from Ossah

Hi there.

This is the way I would do it. I am using hardcoded date values but you will probably want the user to enter them.

I have also put in a little error checking incase the textbox is empty.
i hope this is what your looking for.

Code

Private Sub CommandButton3_Click()
    Dim tempDate As Date, startDate As Date, endDate As Date
    
    'lets say for eg startDate is 01/01/2008 and end date is
    '01/06/2008
    'note these dates are the range you want to search within
    
    startDate = "01/01/2008"
    endDate = "01/06/2008"
    
    'temp date is today lets say. This will be the day you are testing.
    tempDate = Mid(Now, 1, 10)
    
    If CheckBox1 = True And tempDate >= startDate And tempDate <= endDate Then
        If TextBox1.Text <> "" Then
            [Sheet1!H18] = [Sheet1!H18] + TextBox1.Text
        End If
    End If
End Sub

Display More

Display More

Ok, I see what you are doing. My dates will be permanent with the exception of each year my range will change due to the year will change. The ranges will be each quarter of the fiscal year wihich are like this:

1st Qtr = 1 Oct 07 - 31 Dec 07
2nd Qtr = 1 Jan 08 - 31 Mar 08
3rd Qtr = 1 Apr 08 - 30 Jun 08
4th Qtr = 1 Jul 08 - 30 Sep 08

The code I was using does work if you use only one day, (which, daniel c. shows above, which I am not sure where you are getting April since the 5th month is May), but this apparently doesn't work for a range of days. Your code seems to be the code that I may need to use, but would I use the "Else If" command to move to another set of date ranges? I am starting to get confused.

Re: Macro: Check CheckBox Is True, Current Date For Day/Month, Then Sum TextBox & Cell

Quote from daniel.c

Hello.
Change :

Code

If CheckBox1 = True And Today = DateSerial(2008, 5, 19 - 30) Then

to

Code

If CheckBox1 = True And Date = DateSerial(2008, 5, 19 - 30) Then

HTH
Daniel

Display More

Sorry, But that didn't seem to help. The Cell H18 is still not being updated.

Good Morning to All (Especially Dave)!!

I am back with a new issue from our local commander. (Yes, he is a pain). Anyway, I have included a sample of what we are looking at. I am trying to allow the Command Button when clicked to go through multiple conditions before making a decision. So, when someone clicks on Command Button 3 the code should look to see if CheckBox1 is true, then it should check today's date, and if it is between a range of days, or even months, then it would add the number in TextBox1 with the amount already in cell H18. This event will happen every time someone clicks on the Command Button.

The end result is to have several sheets (4 total) for each quarter in the fiscal year, and if the dates are within those parameters, the clicking of the command button will update the correct sheet.

My code doesn't do anything right now. I hope this is the correct way to post a new thread Dave. Please let me know. Thanks for all your help!!

Go Army!!

Re: Drop Down List For Sheet Navigation

Quote from Dave Hawley

J.D, see [fa]*[/fa]

This is default text.

Thanks Dave!! I have read through the links you sent me, and I hope the next time I need a question answered (which should be soon with this officer corps we have here), I will accurately post a new thread, if needed. I will definately check the search area first though. Thanks again Dave for all your help, time, understanding, and patience. Have a great day!!

- J.D.

Re: Drop Down List For Sheet Navigation

Quote from Dave Hawley

No, not "intructions". My comments are code comments that state what the code does. My post and code has nothing to do with buttons ONLY a single ComboBox placed on the Worksheet from the Control Toolbox Toolbar. Add one from the Control Toolbox toolbar and place the code in the Sheet Module housing the ComboBox.

If you want multiple buttons, add drawing objects from the Drawing Toolbar and hyperlink them to a Named Range on each Worksheet.

Or, as stated on the page I linked to, simply right click on the sheet scroll tab and get a built in pop-up menu for sheet navigation.

Good Morning Dave!!

Thanks for your help. I got it to work. I was looking around yesterday on the forum, and pieced together what I thought I needed, and it worked. Here is the code that I used:

Private Sub ComboBox1_Change()
    If ComboBox1.ListIndex > -1 Then Sheets(ComboBox1.Text).Select
End Sub
 
Private Sub ComboBox1_GotFocus()
    Dim objSheet As Object
    On Error Resume Next
    ComboBox1.Clear
     
    For Each objSheet In ThisWorkbook.Sheets
        ComboBox1.AddItem objSheet.Name
    Next objSheet
End Sub


Private Sub CommandButton1_Click()
Sheet60.Cells(1, 1).Value = ""
Sheet60.Activate
End Sub


Private Sub ComboBox2_Change()
    If ComboBox2.ListIndex > -1 Then Sheets(ComboBox2.Text).Select
End Sub
 
Private Sub ComboBox2_GotFocus()
    Dim objSheet As Object
    On Error Resume Next
    ComboBox1.Clear
     
    For Each objSheet In ThisWorkbook.Sheets
        ComboBox2.AddItem objSheet.Name
    Next objSheet
End Sub


Private Sub CommandButton2_Click()
Sheet2.Cells(1, 1).Value = ""
Sheet2.Activate
End Sub

I hope I added the code in here correctly this time. I have added the file below. I do have a few more questions, but I will check the forum first before I post anything. I also was wondering if you could give me the link on naming, and posting thread titles. I saw someone on here get the link for it, but I don't remember exactly where. Thanks again Dave!!

- J.D.

Re: Dependent/Linked Drop Downs In Cells

Quote from Dave Hawley

Don't worry about it, I can be a moron at times. You guys are doing a great job over there and doing so for a good cause.

To select sheets via a ComboBox. Add one from the Control Toolbox toolbar and place this code in the Sheet Module housing the ComboBox.

Code

Private Sub ComboBox1_Change()
'Ensure Chosen Item Is Part Of List. If So, Select the Chosen Sheet
    If ComboBox1.ListIndex > -1 Then Sheets(ComboBox1.Text).Select
End Sub


Private Sub ComboBox1_GotFocus()
Dim objSheet As Object
    On Error Resume Next
'Clear All Prior Sheet Nmaes
        ComboBox1.Clear


'Loop Through Each Sheet and Add Its Name To the ComboBox List
        For Each objSheet In ThisWorkbook.Sheets
            ComboBox1.AddItem objSheet.Name
        Next objSheet
End Sub

Display More

Also, this may be better
Create a Sheet Index of Excel Worksheets

Ok, I see that the instructions have been added to this VBA code, but I am sorry, Dave, I am still new to the code thing. You mention about making sure the item is added to the list. Not really sure what that means. I am not exactly clear on what list I am supposed to add to. I opened the example I sent, and just pasted the code in, and the box clicks like it is supposed to, but the view remains on Menu page. I guess, what I am looking at is when I click on it, it hyperlinks me to Sheet1.

When I click on the combobox on the worksheet named "Menu", then it should take me to the worksheet named "Sheet1", and then I should be able to add multiple comboboxes later for the other operations the commander wants. It just doesn't seem to be working like it is supposed to.

- J.D.

Re: Drop Down List For Sheet Navigation

Quote from Dave Hawley

What button? The code uses a ComboBox Control and takes you to the sheet name chosen.

Good Morning Dave!

Ok, on the Menu sheet, there is the box with the word Issue on it. When I click that box, or button, then I should be moved to Sheet1. It really doesn't where on sheet1 just that I move there. Sooner or later, there will be several boxes on Menu worksheet, and each one will take you to a different section within the workbook. I may have more questions later, but I will double check the search section before I make a mistake and get myself banned.

- J.D.

Re: Dependent/Linked Drop Downs In Cells

Ok I tried it and the button works, but where in the code do I tell it that if I hit that button to take me to the other sheet. For example, in the included document, how do I get the button to take me to Sheet1? Thanks again Dave for all your help.

- J.D.

Re: Dependent/Linked Drop Downs In Cells

Quote from Dave Hawley

Don't worry about it, I can be a moron at times. You guys are doing a great job over there and doing so for a good cause.

To select sheets via a ComboBox. Add one from the Control Toolbox toolbar and place this code in the Sheet Module housing the ComboBox.

Code

Private Sub ComboBox1_Change()
    If ComboBox1.ListIndex > -1 Then Sheets(ComboBox1.Text).Select
End Sub


Private Sub ComboBox1_GotFocus()
Dim objSheet As Object
    On Error Resume Next
        ComboBox1.Clear


        For Each objSheet In ThisWorkbook.Sheets
            ComboBox1.AddItem objSheet.Name
        Next objSheet
End Sub

Display More

Also, this may be better
Create a Sheet Index of Excel Worksheets

I will give it a shot, still having problems with the first one, but I will try to get back to it in a few minutes. We have a VTC with the Division, and some of us are required to go, so I will check back later. Thanks again Dave!!

Ok I tried it and the button works, but where in the code do I tell it that if I hit that button to take me to the other sheet. For example, in the included document, how do I get the button to take me to Sheet1?

Split off from
http://www.ozgrid.com/forum/showthread.php?t=90171

Quote from Dave Hawley

Try it now. You hadn't named the cells that the Validation lists are Dependant on. Not bad for a "moron", hey?

:oops: Sorry about that. Things get pretty stressed around here, especially when your commander depends on you as much as mine, and just between the two of us, it is twice as hard when your commander is younger than you. Let me try it real quick.[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]Ok, that works great, now I have to go back and add all the real units and soldiers. Fun, fun, fun. BTW, you wouldn't happen to know how to add a first worksheet that would have buttons to take you to the different sections? I know how to do the hyperlinks, but I think I read something on here about comboboxes?? If I need to post this somewhere else, then just push me in the right direction. I am use to pushing in the Army, it's our life!!

I thank you Dave, and the Army thanks you as well. Thanks for making life here a little easier.

Re: Dependent/Linked Drop Downs In Cells

Quote from Dave Hawley

Attach an example Workbook that you believe should work.

BTW, I edited my last post and perhaps the edit helps?

Ok, here is an example copy of the original. Keep in mind that due to security issues, I am not allowed to use real unit names, or personnel names, so all the units and soldiers names have been changed to protect our forces. Thanks for your help Dave!

Re: Dependent/Linked Drop Downs In Cells

Quote from Dave Hawley

RFDisturbance, code tags are used like [noparse]

Code

ALL CODE HERE

[/noparse] Please use them as agreed.

Oops, sorry Dave. I am not too good on these forum things.

In regards to the code not working, maybe I should mention that the second drop down menu is extracting its info from other worksheets in the workbook. Not sure if that changes anything.

Re: Dependent/Linked Drop Downs In Cells

Quote from Dave Hawley

The advanced example in the download on the page does.
http://www.ozgrid.com/Excel/dependent-lists.htm

Ok, I tried the code on the last wroksheet under the advanced spreadsheet example, but it still will not clear the second menu. It stays on the soldier that was originally selected before. Here is what I put in:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim strName As String
    If Target.Cells.Count > 1 Then Exit Sub
        On Error Resume Next
        strName = Target.Name.Name
        On Error GoTo 0
        
    If strName = "B3" Then
       Application.EnableEvents = False
       Range("B8") = vbNullString
       Application.EnableEvents = True
    End If
End Sub

B3 = Where my first drop down menu is, which is the Unit
B8 = Where the second drop down menu is, which is the Soldiers

It just doesn't want to clear the second menu when a different unit is selected.

Re: Drop Down Menus

Quote from AAE

Have a look at Dependent Lists using Data Validation.

Not as lean or as mean, but still a Marine! Thank you for serving.

HTH,

That helps, but I noticed that the drop down menus do not clear themselves if you chose a different unit in the first block. Is there a code for this or a formula to allow this? Also, how do I make a button to where if I click on it, it will take me to the worksheet. I know how to do a hyperlink, but I have seen some people you I guess it is called a combobox, but I don't know anything about how to use this stuff. Thanks for your help so far. Greatly appreciated!!

Good Morning Everyone!!

I am back with a new issue. The macro that was prvided from this website was great and provided my commander with the info he needed to make life in Iraq a little easier.

Now he has another task that I am not having any success with. He has asked if we can make an Excel spreadsheet that allows us to access a list of customer units from a drop down menu which is in another workbook, and once you choose that particular unit, another drop down menu will allow only those customers who are assigned to that unit to be selected from that sub-menu. I hope that makes sense.

Here is an example:

Say customer A1 (we will call him Delta Co, 2-1 Aviation) is selected from this first drop down menu (which I haven't figured out how to make yet either). Then this selection would access another worksheet in that other workbook with a new drop down menu with only the four customers in that unit. As an example we will use the names SSG Wilson, SGT Jackson, SFC Jones, and SGT Phillips.

So when the Supply Tech here opens the spreadsheet, the first drop down menu, he will select Delta Co, 2-1 Aviation. Then the next menu will only show four names, and he will select SFC Jones.

Once we get this going, I may be able to figure out the other things the commander wants, if not I will be back.

Thanks in advance, and thanks for your support!!

GO ARMY!!

Re: A Macro That Will Send E-mail When Cell Is A Certain Value

Well, it works like a charm. Thank you so much!! The commander loves it and he said kudos to you for working with us so quickly. If any changes or modifications to the original request comes up, I will be back in here asking for some help again. I do like the idea of the MsgBox, so I will put that in as well. Thanks again for everything!!

PS. As far as being patient on the wins, trust me I know, I am still waiting on my Dolphins to win at least one game and the Patriots to lose one game to protect my '72 Dolphins record. LOL!! Take care!!

Re: A Macro That Will Send E-mail When Cell Is A Certain Value

Ok, new update. I think I got it to work. Here is the problem. We use our ID Cards, which have a computer chip in it, to login to any military computer allowing certain security priviledges. Every now and then the card will get stuck in a loop and will not be readable. Because of this the outlook will not work until you remove the card and reinput your pin. It seems to be doing great, but I will final test it tomorrow, run it by the commander, and give you a heads up of what he says. For now, we are changing shifts, and I am ready to get out of the office and back in my bunk. I will let you know, thanks again!!

Re: A Macro That Will Send E-mail When Cell Is A Certain Value

Ok, here is the update. I recopied the code, and it said it was sending, and then ....nothing. It was the first time my computer and myself both had the deer in the headlights look. I deleted the comment in the cell, and saved and tried to open the sheet, and it still acted like it ran with no problems. At least this time the run-time error did not come up. Any ideas?

Re: A Macro That Will Send E-mail When Cell Is A Certain Value

First of all, thanks for your help with this. Cell C13 contains the actual email address in blue and underlined (looks like a hyperlink to me). As far as the sending multiple e-mails, I have decided to use my e-mail address so I don't have every Captain, Major, LT Colonel, and Colonel screaming at me for loading their inbox with messages (also to prevent them from disregarding these e-mails thinking they are just a test especially once this thing is up and running). Another thing is when I get the run time error, I click the debug button and it highlights the .Send command. I am not sure why it won't extract the e-mail address out of C13, unless it needs to be a standard input, and not the hyperlink Excel likes to automatically create when any website or e-mail is typed in. I also tried the .Display command and it does pull the e-mail address from the C13 cell, but that kind of defeats the purpose of doing everything automatically. One other thing (and I know I am probably getting on your nerves now) is that Worksheet 1 is known as Main Menu, and should be skipped. The code is trying to send an e-mail to nobody cause it reads the main menu page first. I would love to send you a copy, but due to security reasons I can't send the spreadsheet with our unit's numbers on it. Sorry. Please help when you get a chance. Thanks again!!

PS. Why can't the Army win just one time against the Navy?? Can you guys just let us win so we can pretend like our team isn't that bad??