UserForm to view contents of spreadsheet

[mushybrain]

Hello all

I have a userform currently that I enter data into my speadsheet with, however the spreadsheet itself is getting way too crowded and is not easily navigable by the untrained eye.

Is there a way of getting a userform to show the contents of selected rows in a worksheet as in a database table-form relationship?? I have noticed another thread on this and someone mentioned a "microsoft office spreadsheet control" that can be built in to worksheets, but alas I dont think I have this add-in, or cannot find it in my installation of Excel anyway.

Can anyone shed some light on the situation???

Many thanks

Jon

[mushybrain]

Or does anyone know if this is actually possible at all in Excel??

Help!

[funkymonkUK]

Hi

I managed to do it with out an add-inn . what i did was link a cell and make that cell one of the text boxs values then what i did what create a unique ID with this i managed to create a search engine which finds the unique ID and then changes the textboxs to the value of those cells.



Sheets("Data").Visible = True

Sheets("data").Select
Columns("o:o").Select
Id = Range("x5").Value ' i put in the following in this cell =x4&x5

Set abc = Selection.Find(What:=Id, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False)

If abc Is Nothing Then
Sheets("Data").Select
Range("a2").Select

' finds end of data list and then inserts a new record

Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = court.Value
ActiveCell.Offset(0, 1).Value = Month.Value
ActiveCell.Offset(0, 2).Value = Year.Value
ActiveCell.Offset(0, 3).Value = CJHours.Value
ActiveCell.Offset(0, 4).Value = DJHours.Value
ActiveCell.Offset(0, 5).Value = HCHours.Value
ActiveCell.Offset(0, 6).Value = THours.Value
ActiveCell.Offset(0, 7).Value = MHours.Value
ActiveCell.Offset(0, 8).Value = Chours.Value
ActiveCell.Offset(0, 9).Value = OHours.Value
ActiveCell.Offset(0, 10).Value = Total.Value
ActiveCell.Offset(0, 11).Value = "Yes"
Else
abc.Activate
CommandButton1.Enabled = False
CommandButton5.Enabled = True
Beep
Reply = MsgBox("You have already entered in " & court.Value & " for " & Month.Value & " " & Year.Value & ". Would you like to change this information?", vbYesNo)

If Reply = vbYes Then
admin = InputBox("What is the Password?")

If admin = "password" Then

CJHours.Value = ActiveCell.Offset(0, -11).Value
DJHours.Value = ActiveCell.Offset(0, -10).Value
HCHours.Value = ActiveCell.Offset(0, -9).Value
THours.Value = ActiveCell.Offset(0, -8).Value
MHours.Value = ActiveCell.Offset(0, -7).Value
Chours.Value = ActiveCell.Offset(0, -6).Value
OHours.Value = ActiveCell.Offset(0, -5).Value
Total.Value = ActiveCell.Offset(0, -4).Value
CommandButton7.Enabled = False
CommandButton10.Enabled = True

Else

MsgBox ("Sorry you do not have Authorisation")
End If

End If

End If
End Sub


if you need any help just shout.....

l

[mushybrain]

Thanks for your reply dude. I think the code you listed there works, but only for one row at a time - and you have to search every time to get your data required for the userform.

What I require is a real-time update of data, for example I have my unique id for every service call in column A. I then have all my address and contact details etc of that service call on the same row.

I want to select any unique id in column A with the mouse / arrow keys, whatever... and automatically the address and other relevant details are updated in text boxes on a form on the screen.

So essentially I need a form that is permanently on the screen that references the .value of the activecell and those cells offset in the same row - like a database!

Ideally I should import all the tables into a database and start from there, but the job would be immense as there are too many separate sheets and vb code to redesign and start again.

Any further help appreciated!