Display data on user input dialogue box.

[mysterious-dr-x]

Once again the internet has been entirely unhelpful with regards to my current issue.

I am creating a user input form so that information can be added to a secondary workbook from the first without having to do it all manually.

Now, the secondary workbook looks something like this, with the user input form inserting data into the next available row.

In the example above, 317 is the next available row (I should probably mention that this is currently in excel row 33, but this is not fixed).

Most of it works fine, there is only one thing I don't know how to do & that is to display the DCRC reference number (eg 317) in the indicated box in the form below.

Currently this is a user input box, but that can be changed if required. In addition I'd like to paste this number into a cell (lets say A1) in the primary workbook, although I suspect that this part will be much simpler.

This should be done upon opening the dialogue, or by clicking the "Find" button, either is fine by me.

Please let me know if you feel that you require any of the code that I already have.



Edit: As it turns out there are two things that I don't know how to do.

The 2nd is to make the submit button not just submit the information (which it does fine), but also to close the dialogue box..... how do I do this?

 

[tigeravatar]

mysterious-dr-x,

In the userform Initialize event, you can populate the textbox for the DCRC number with the following. Code assumes that both workbooks are open, DCRC numbers are in column A, Dates are in column B, and the DCRC textbox is named txtDCRC:

Private Sub UserForm_Initialize()
    Static wb2 As Workbook: Set wb2 = Workbooks("Secondary Workbook Name.xls")
    Me.txtDCRC.Text = wb2.Sheets("Sheet1").Cells(Rows.Count, "B").End(xlUp).Offset(1, -1).Value
End Sub

As for closing the userform when the submit button is clicked, at the end of the submit button code, add the line:

    Unload Me

Hope that helps,
~tigeravatar

 

[mysterious-dr-x]

Private Sub UserForm_Initialize()
    Static wb2 As Workbook: Set wb2 = Workbooks("Secondary Workbook Name.xls")
    Me.txtDCRC.Text = wb2.Sheets("Sheet1").Cells(Rows.Count, "B").End(xlUp).Offset(1, -1).Value
End Sub

Gives me an error......"Run-time error '9': subscript out of range"

also, if i close vb editor, then reopen & run i get "Run-time error '424': object required" followed by 9 if run again.


EDIT: wait, i think that is my fault, I managed to to copy a rather major part of that code. let me give it another go

 

[tigeravatar]

Questions:

1. Are both workbooks open?
2. Did you change workbooks("Secondary Workbook Name.xls") to be the actual name of the secondary workbook?
3. Did you change Me.txtDCRC to be the actual name of the intended textbox?
4. Did you change wb.Sheets("Sheet1") to be the actual name of the worksheet in the secondary workbook that contains the DCRC numbers?
5. Are the DCRC numbers in column A and the dates in column B of that worksheet?

 

[mysterious-dr-x]

EDIT: wait, i think that is my fault, I managed to to copy a rather major part of that code. let me give it another go

Well, I did leave out a significant piece of code, having put it in, it still gives me those errors.

 

[mysterious-dr-x]

Questions:

Assuming that I only need to change Secondary Workbook Name, txtDCRC & Sheet1, yes, yes, yes, yes & yes :/

 

[tigeravatar]

Weird. I created a test book with info that matches the data you posted and saved it as a .xls file. Then I created a second workbook which contains the userform. The code ran and the textbox populated successfully for me. Only thing I can think of is does the secondary workbook have a different file extension than .xls?

 

[mysterious-dr-x]

Only thing I can think of is does the secondary workbook have a different file extension than .xls?

Ah, that is indeed the problem. It is .xlsx

To check this is the problem, I re-saved as .xls & it worked fine, then I tried with .xlsx in the code & it only gave me errors for the second one (both were open).

 

[tigeravatar]

Glad to hear its working

 

[mysterious-dr-x]

Glad to hear its working

Thanks for the help.