Excel Form HELP

[Roger Converse]

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 are:

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

 

[FSt1]

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

 

[FSt1]

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

 

[Roger Converse]

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