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