forms: unknown control source

B

bezza7

New Member
Joined
Aug 25, 2006
Messages
22
Hi guys

I am creating a form for a spreadsheet and have designed it but now i need to set tthe control cource for each control. The thing is the control source will change every time as I am using this to add entries like a database. It will have to input the data from the form into the next available empty line e.g. at the bottom of the table.

I am guessing I have to write some script but I am not sure how to proceed.

Can anyone help?

Cheers
Al
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Sort by date Sort by votes
This is the code I use as the basis of a new project :-
NB: THe base data is a single table with headings in row 1
Code: 
'==================================================================
'- USERFORM TO UPDATE EXCEL WORKSHEET DATABASE
'- basic info. for table navigation only - will need improvement
'- by Brian Baulsom
'- ** NEXT MESSAGE** added button to FIND a record **
'-----------------------------------------------------------------
'- This example has 3 textboxes named TextBox1, ...etc
'- + 3 buttons - [Next],[Previous],[New]
'- Uses variable 'CurrentRow' to refer to worksheet table
'==================================================================
Dim DataSheet As Worksheet
Dim CurrentRow As Long
Dim LastRow As Long

'==========================================================
'- BUTTON SUBROUTINE : ADD NEW RECORD BOTTOM OF TABLE
'==========================================================
Private Sub NewRecordButton_Click()
    UpdateTable
    LastRow = LastRow + 1
    CurrentRow = LastRow
    UpdateForm
End Sub

'==========================================================
'- BUTTON SUBROUTINE : GO TO NEXT RECORD
'==========================================================
Private Sub NextRecordButton_Click()
    UpdateTable
    If CurrentRow < LastRow Then
        CurrentRow = CurrentRow + 1
        UpdateForm
    End If
End Sub

'==========================================================
'- BUTTON SUBROUTINE : GO TO PREVIOUS RECORD
'==========================================================
Private Sub PreviousRecordButton_Click()
    UpdateTable
    If CurrentRow > 2 Then
        CurrentRow = CurrentRow - 1
        UpdateForm
    End If
End Sub

'==========================================================
'- INITIALISE FORM WITH FIRST RECORD
'==========================================================
Private Sub UserForm_Initialize()
    Set DataSheet = Worksheets("Database")
    CurrentRow = 2  ' first row with data
    LastRow = DataSheet.Range("A65536").End(xlUp).Row
    UpdateForm
End Sub

'==========================================================
'- SUBROUTINE : WORKSHEET TABLE DATA TO FORM
'==========================================================
Private Sub UpdateForm()
    TextBox1.Value = DataSheet.Cells(CurrentRow, 1).Value
    TextBox2.Value = DataSheet.Cells(CurrentRow, 2).Value
    TextBox3.Value = DataSheet.Cells(CurrentRow, 3).Value
End Sub

'==========================================================
'- SUBROUTINE : FORM DATA TO WORKSHEET TABLE
'- changes all cells even though data may be the same
'==========================================================
Private Sub UpdateTable()
    DataSheet.Cells(CurrentRow, 1).Value = TextBox1.Value
    DataSheet.Cells(CurrentRow, 2).Value = TextBox2.Value
    DataSheet.Cells(CurrentRow, 3).Value = TextBox3.Value
End Sub
 
Upvote 0
Additional button to find a value and put the record into the userform.
Code: 
'==========================================================
'- BUTTON SUBROUTINE : FIND RECORD & PUT TO FORM
'- uses [FIND] button named "FindButton"
'- looks in column A for match.
'==========================================================
Private Sub FindButton_Click()
    Dim FoundCell As Object
    Dim MyValue As String
    '------------------------------------------------------
    '- get value
    MyValue = InputBox("Value to find in column A", " FIND")
    If MyValue = "" Then Exit Sub
    '-------------------------------------------------------
    '- find value
    With DataSheet.Range("A:A")
        Set FoundCell = .Find(MyValue, LookIn:=xlValues, lookat:=xlPart)
    End With
    '-------------------------------------------------------
    '- check found
    If FoundCell Is Nothing Then
        MsgBox (MyValue & " not found")
    Else
        CurrentRow = FoundCell.Row
        UpdateForm
    End If
End Sub
'============ EOP =========================================
 
Upvote 0
REPLY TO PRIVATE MESSAGE FROM EJ42955<SCRIPT type=text/javascript> vbmenu_register("postmenu_", true); </SCRIPT> REFERRING ME TO THIS (NB.dated May 2007) BUT WITH NO OTHER MESSAGE
I have not seen the message before - probably because I generally only read messages with no replies. Although I do not encourage PMs asking for private response I feel it is perfectly valid to refer me to a message as EJ has done.
---------------------------------------------------------------------
EJ
Sorry for slow response. I am presently working on a project with little time to spare.
It would have helped if you had been clearer about what you wanted.
If as Bezza7 you want to refer to a different worksheet from the same form you just need to change the 'DataSheet' variable to point to something else. eg......
Code: 
 '- PARTIAL CODE
Set DataSheet=Workbooks("Book1.xls").Worksheets("Sheet1")
'- or ...........
Set DataSheet=Workbooks("Book2.xls").Worksheets("Sheet2")
 
Upvote 0
No problem, Brian. I understand other priorities. I'm grateful for any assistance from this site.

My needs are a little different than Bezza7. But, your code was a terrific jumping off point.

Right now, I'm not concerned with changing datasheets. I plan to create buttons on the form, that jumps to the sheet that corresponds to the book.

What I'm trying to do is, after the find code runs and places the appropriate data on the form, I need to add additional data to boxes in the form, and have that update cells on the same row as the "FoundCell".

I really, do appreciate the help. And, everytime I get some piece of code, it usually sends me in the right direction to make other changes.

Thanks, for your help,
EJ
 
Upvote 0
OK. Then, I've screwed up something. cmdFind_Click, finds associate and writes data to form. cmdEdit_Click stops at, ws.Cells(iRow, 2).Value = Me.txtBook.Value
And, error message reads "Run-time error '1004': Application-defined or object-defined error"

What did I do wrong?

Code: 
Private Sub cmdFind_Click()

    Dim FoundCell As Object
    Dim MyValue As String
    Dim ws As Worksheet
    Dim datasheet As Worksheet

Set ws = ThisWorkbook.ActiveSheet
    
    '------------------------------------------------------
    '- get value
    MyValue = InputBox("Find Associate", " FIND")
    If MyValue = "" Then Exit Sub
    '-------------------------------------------------------
    '- find value
    Set datasheet = ThisWorkbook.ActiveSheet
    With datasheet.Range("D:D")
        Set FoundCell = .Find(MyValue, LookIn:=xlValues, lookat:=xlPart)
    End With
    '-------------------------------------------------------
    '- check found
    If FoundCell Is Nothing Then
        MsgBox (MyValue & " not found")
        Else
            CurrentRow = FoundCell.Row
            UpdateForm

    End If
    
'    If FoundCell = MyValue Then
'            CurrentRow = FoundCell.Row
'            FoundCell.SetFocus
'            UpdateForm
'        Else
'        MsgBox (MyValue & " not found")
'    End If
    
End Sub

Code: 
Private Sub cmdEdit_Click()

    Dim iRow As Long
    Dim ws As Worksheet
    Dim datasheet As Worksheet

Set ws = ThisWorkbook.ActiveSheet

' check for a book number
If Trim(Me.txtBook.Value) = "" Then
    Me.txtBook.SetFocus
    MsgBox "Please enter a Book Number"
    Exit Sub
End If

If Trim(Me.txtAssociate.Value) = "" Then
    Me.txtAssociate.SetFocus
    MsgBox "Please enter an Associate"
    Exit Sub
End If


'copy the data to the worksheet
ws.Cells(iRow, 2).Value = Me.txtBook.Value
ws.Cells(iRow, 4).Value = Me.txtAssociate.Value
'ws.Cells(iRow, 5).Value = Me.txtCheckOut.Value
ws.Cells(iRow, 6).Value = Me.txtSentCondition.Value
ws.Cells(iRow, 8).Value = Me.txtReturnDate.Value
ws.Cells(iRow, 9).Value = Me.txtReturnCond.Value
ws.Cells(iRow, 10).Value = Me.txtNotes.Value


'clear the data from the form
Me.txtBook.Value = ""
Me.txtAssociate.Value = ""
'Me.txtCheckOut.Value = ""
Me.txtSentCondition.Value = ""


End Sub

Thanks
EJ
 
Upvote 0
Several possible errors.Check names exist.Should be using Private Sub UpdateTable() and code like it.You have not set iRow (why are you trying to use this instead of CurrentRow?)I suggest you go back to my original and use that because it gives a fully working stucture. i.e. you just need to add code to do your checks etc.There is no need for an Edit button unless you want it to run Private Sub UpdateTable().
 
Upvote 0
Brian,
Thanks, so much, for your help and patience. I see now, that your code does a lot more than meets the eye. I've been able to tweak a couple of things. And, it's working great.

Thanks, again,
EJ
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Question Question
Check box form control - alignment format
Replies
5
Views
148
Skyybot
Skyybot
M
  • Question Question
Controlling TabStop
Replies
3
Views
370
RoryA
RoryA
D
  • Solved
Using VLOOKUp to find a Date in a Data Worksheet
2
Replies
13
Views
195
Alex Blakenburg
A
S
  • Question Question
User Form not Loading
Replies
5
Views
177
StevieMP
S
J
  • Solved
Excel VBA Form Add data to a specific range
Replies
2
Views
257
JJCam
J

Forum statistics

Threads
1,224,506
Messages
6,179,159
Members
452,892
Latest member
yadavagiri

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top