Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Form HELP
Hello,
I am going way outside of my comfort zone and am attempting to create a form in Excel. The original form was posted in another forum, and I liked what the form did so I thought I could the basics and apply what I wanted. Well, that is not proving to go so smoothly. Basically, I am looking for the form to open (when the button is clicked) with data attached to the combo box, find the value the user either enters or chooses and then create a table based on the infomation in that record. I could create this in Access, but I am pretty much the only person in my company that uses Access. Therefore, I am trying to do this in Excel, which is what everyone in my office is familiar with. My beginning questions a 1.) How do I set the combobox to a specific range of cells? 2.) Even though I don't have the combobox tied to anything, the search function will still find a match. However when the code goes to (or is supposed to) find the correct range, it doesn't appear to be happening as I step through the code. Which is weird, because it is the exact same code that is used in a different macro. 3.) Once the correct range is selected, how do I translate that into a table? I am looking into the offset function, because it seems to be used with a lot of charts, but I really don't know much about it yet. I thought this would be a little more similar to Access. :( Any assistance would be greatly appreciated. Thank you, Roger Here is the sample code. It needs a lot of help. Dim strFind 'what to find Dim rSearch As Range 'range to search Dim lngMonth As Long ' for when January rolls around and the selection cirteria Set rSearch = Sheet1.Range("b1", Range("b65536").End(xlUp)) strFind = Me.cboxType.Value 'what to look for Dim f As Integer With rSearch Set c = .Find(strFind, LookIn:=xlValues) If Not c Is Nothing Then 'found it c.Select lngMonth = 0 lngMonth = Format(Date, "MM") - 1 If lngMonth = 1 Then Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlToRight)).Select Else Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlToRight)).Select End If End If End sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Form HELP
hi,
1. on the sheet that you have your db on... off to the side somewhere put in a column all the things you want in the combo box. lets say it is sheet1 range x1:x10 in the vb editor, call up your form, right click the combo box, click properties and set the rowsource to Sheet2!X1:X10. 2. not sure here. since i don't have the combobox i changed that to "x". the code is finding it ok. so i'm guessing that with nothing in the box and if your are not typing in something, then there was nothing to find. guessing. 3. problems with code. 1.this line.... Range(Selection, Selection.End(xlToRight)).Select it appears 5 times in your if statement. and they are on both sides of the else. to me that is like saying..... If lngMonth = 1 Then do this else so the same thing end if the whole if statement can be replace with.... If lngMonth = 1 Then range(c, c.end(xltoright)).select end if lngMonth = Format(Date, "MM") - 1 current date formated to month minus 1 is november(11). not sure if you knew that. how to get it in a db? add this lines to bottom. untested but should work. Selection.copy Sheets("sheet2").activate Range("A65000").end(xlup).offset(1,0).pastespecial xlpasteall it's now in your db. I'm watching bowl games right now but i will be back in from time to time. post back if you have questions. Regards FSt1 "Roger Converse" wrote: Hello, I am going way outside of my comfort zone and am attempting to create a form in Excel. The original form was posted in another forum, and I liked what the form did so I thought I could the basics and apply what I wanted. Well, that is not proving to go so smoothly. Basically, I am looking for the form to open (when the button is clicked) with data attached to the combo box, find the value the user either enters or chooses and then create a table based on the infomation in that record. I could create this in Access, but I am pretty much the only person in my company that uses Access. Therefore, I am trying to do this in Excel, which is what everyone in my office is familiar with. My beginning questions a 1.) How do I set the combobox to a specific range of cells? 2.) Even though I don't have the combobox tied to anything, the search function will still find a match. However when the code goes to (or is supposed to) find the correct range, it doesn't appear to be happening as I step through the code. Which is weird, because it is the exact same code that is used in a different macro. 3.) Once the correct range is selected, how do I translate that into a table? I am looking into the offset function, because it seems to be used with a lot of charts, but I really don't know much about it yet. I thought this would be a little more similar to Access. :( Any assistance would be greatly appreciated. Thank you, Roger Here is the sample code. It needs a lot of help. Dim strFind 'what to find Dim rSearch As Range 'range to search Dim lngMonth As Long ' for when January rolls around and the selection cirteria Set rSearch = Sheet1.Range("b1", Range("b65536").End(xlUp)) strFind = Me.cboxType.Value 'what to look for Dim f As Integer With rSearch Set c = .Find(strFind, LookIn:=xlValues) If Not c Is Nothing Then 'found it c.Select lngMonth = 0 lngMonth = Format(Date, "MM") - 1 If lngMonth = 1 Then Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlToRight)).Select Else Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlToRight)).Select End If End If End sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Form HELP
hi
forgot to mention something on point 3. with this line... lngMonth = Format(Date, "MM") - 1 (current date -1) and this line in your if statement..... If lngMonth = 1 Then..... this code will only copy to the database in januarry unless you go back to the redundent if this then that else the same thing. meaning you have a lot of redundent code that is unnecessary . not sure if you understood that. regards FSt1 "FSt1" wrote: hi, 1. on the sheet that you have your db on... off to the side somewhere put in a column all the things you want in the combo box. lets say it is sheet1 range x1:x10 in the vb editor, call up your form, right click the combo box, click properties and set the rowsource to Sheet2!X1:X10. 2. not sure here. since i don't have the combobox i changed that to "x". the code is finding it ok. so i'm guessing that with nothing in the box and if your are not typing in something, then there was nothing to find. guessing. 3. problems with code. 1.this line.... Range(Selection, Selection.End(xlToRight)).Select it appears 5 times in your if statement. and they are on both sides of the else. to me that is like saying..... If lngMonth = 1 Then do this else so the same thing end if the whole if statement can be replace with.... If lngMonth = 1 Then range(c, c.end(xltoright)).select end if lngMonth = Format(Date, "MM") - 1 current date formated to month minus 1 is november(11). not sure if you knew that. how to get it in a db? add this lines to bottom. untested but should work. Selection.copy Sheets("sheet2").activate Range("A65000").end(xlup).offset(1,0).pastespecial xlpasteall it's now in your db. I'm watching bowl games right now but i will be back in from time to time. post back if you have questions. Regards FSt1 "Roger Converse" wrote: Hello, I am going way outside of my comfort zone and am attempting to create a form in Excel. The original form was posted in another forum, and I liked what the form did so I thought I could the basics and apply what I wanted. Well, that is not proving to go so smoothly. Basically, I am looking for the form to open (when the button is clicked) with data attached to the combo box, find the value the user either enters or chooses and then create a table based on the infomation in that record. I could create this in Access, but I am pretty much the only person in my company that uses Access. Therefore, I am trying to do this in Excel, which is what everyone in my office is familiar with. My beginning questions a 1.) How do I set the combobox to a specific range of cells? 2.) Even though I don't have the combobox tied to anything, the search function will still find a match. However when the code goes to (or is supposed to) find the correct range, it doesn't appear to be happening as I step through the code. Which is weird, because it is the exact same code that is used in a different macro. 3.) Once the correct range is selected, how do I translate that into a table? I am looking into the offset function, because it seems to be used with a lot of charts, but I really don't know much about it yet. I thought this would be a little more similar to Access. :( Any assistance would be greatly appreciated. Thank you, Roger Here is the sample code. It needs a lot of help. Dim strFind 'what to find Dim rSearch As Range 'range to search Dim lngMonth As Long ' for when January rolls around and the selection cirteria Set rSearch = Sheet1.Range("b1", Range("b65536").End(xlUp)) strFind = Me.cboxType.Value 'what to look for Dim f As Integer With rSearch Set c = .Find(strFind, LookIn:=xlValues) If Not c Is Nothing Then 'found it c.Select lngMonth = 0 lngMonth = Format(Date, "MM") - 1 If lngMonth = 1 Then Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlToRight)).Select Else Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlToRight)).Select End If End If End sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Form HELP
Thank you very much for the replies. I will be heading off to the office
shortly so I can concentrate on this, so I will probably have some questions later if you are bored. ;o) The redundancy in the lngMonth if/Else code is due to how I select the data. The file looks like this: Vendor YTD Jan Feb Mar A 95 98 97 99 B 96 97 96 95 When I recorded the macro, it takes me two times holding ctrl + shift + right arrow to grab all the data I want for Jan. If the month is past January, I have to hit that combination three times. If there is a simpler way (once the vendor ID is found) to capture the applicable range of cells to the right, (and I am sure there is) I am all for it. I think I mentioned in my initial post that this is well outside of my knowledge base (which is mostly in Access), so I am just "hacking" at this and trying things to get it to work. Thanks, Roger "FSt1" wrote: hi forgot to mention something on point 3. with this line... lngMonth = Format(Date, "MM") - 1 (current date -1) and this line in your if statement..... If lngMonth = 1 Then..... this code will only copy to the database in januarry unless you go back to the redundent if this then that else the same thing. meaning you have a lot of redundent code that is unnecessary . not sure if you understood that. regards FSt1 "FSt1" wrote: hi, 1. on the sheet that you have your db on... off to the side somewhere put in a column all the things you want in the combo box. lets say it is sheet1 range x1:x10 in the vb editor, call up your form, right click the combo box, click properties and set the rowsource to Sheet2!X1:X10. 2. not sure here. since i don't have the combobox i changed that to "x". the code is finding it ok. so i'm guessing that with nothing in the box and if your are not typing in something, then there was nothing to find. guessing. 3. problems with code. 1.this line.... Range(Selection, Selection.End(xlToRight)).Select it appears 5 times in your if statement. and they are on both sides of the else. to me that is like saying..... If lngMonth = 1 Then do this else so the same thing end if the whole if statement can be replace with.... If lngMonth = 1 Then range(c, c.end(xltoright)).select end if lngMonth = Format(Date, "MM") - 1 current date formated to month minus 1 is november(11). not sure if you knew that. how to get it in a db? add this lines to bottom. untested but should work. Selection.copy Sheets("sheet2").activate Range("A65000").end(xlup).offset(1,0).pastespecial xlpasteall it's now in your db. I'm watching bowl games right now but i will be back in from time to time. post back if you have questions. Regards FSt1 "Roger Converse" wrote: Hello, I am going way outside of my comfort zone and am attempting to create a form in Excel. The original form was posted in another forum, and I liked what the form did so I thought I could the basics and apply what I wanted. Well, that is not proving to go so smoothly. Basically, I am looking for the form to open (when the button is clicked) with data attached to the combo box, find the value the user either enters or chooses and then create a table based on the infomation in that record. I could create this in Access, but I am pretty much the only person in my company that uses Access. Therefore, I am trying to do this in Excel, which is what everyone in my office is familiar with. My beginning questions a 1.) How do I set the combobox to a specific range of cells? 2.) Even though I don't have the combobox tied to anything, the search function will still find a match. However when the code goes to (or is supposed to) find the correct range, it doesn't appear to be happening as I step through the code. Which is weird, because it is the exact same code that is used in a different macro. 3.) Once the correct range is selected, how do I translate that into a table? I am looking into the offset function, because it seems to be used with a lot of charts, but I really don't know much about it yet. I thought this would be a little more similar to Access. :( Any assistance would be greatly appreciated. Thank you, Roger Here is the sample code. It needs a lot of help. Dim strFind 'what to find Dim rSearch As Range 'range to search Dim lngMonth As Long ' for when January rolls around and the selection cirteria Set rSearch = Sheet1.Range("b1", Range("b65536").End(xlUp)) strFind = Me.cboxType.Value 'what to look for Dim f As Integer With rSearch Set c = .Find(strFind, LookIn:=xlValues) If Not c Is Nothing Then 'found it c.Select lngMonth = 0 lngMonth = Format(Date, "MM") - 1 If lngMonth = 1 Then Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlToRight)).Select Else Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlToRight)).Select End If End If End sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel form. Need data extracted to spreadsheet each time a form co | Excel Discussion (Misc queries) | |||
Can a form made in Excel 2002 be converted into a fillable form? | Excel Discussion (Misc queries) | |||
how to generate a unique form # when using an excel form template | Excel Worksheet Functions | |||
Transfer data to form from Excel range upon loading of form. | Excel Programming | |||
form in excel to be attached to the emails address in the form upon sumission | Excel Programming |