displaying data base entries in the userform

Rob Mann

Board Regular
Joined
Jan 10, 2011
Messages
63
Hi.
Below is some code ive got set in a user form. In a module i have some code to find an entry in column c. When it finds that value, that cell becomes the active cell. The code below then uses the offset function to load the user form data into the the data base. What i need to do is to display on this user form the value of column "L" in the text box I have named "txtrecol" could anyone suggest ways of changing this code so that its possible to see the entry from the userform? I can allow column "L" to become the active cell though. Column "C" has to remain the active cell.

Private Sub cmdcancel_Click()
Unload Me
End Sub

Private Sub cmdclearform_Click()
Call UserForm_initialize
End Sub

Private Sub cmdok_Click()
ActiveWorkbook.Sheets("COMPLAINTS").Activate
ActiveCell.Offset(0, 11) = recondate.Value
ActiveCell.Offset(0, 12) = txttime.Value
ActiveCell.Offset(0, 13) = txtdateonsite.Value
ActiveCell.Offset(0, 14) = cboccar.Value
ActiveCell.Offset(0, 15) = txtmiles.Value
ActiveCell.Offset(0, 16) = txttravel.Value
ActiveCell.Offset(0, 17) = txtnotes.Value
Unload Me
End Sub


Private Sub UserForm_initialize()
ActiveWorkbook.Sheets("COMPLAINTS").Activate
Me.txtjobnum = ActiveCell.Value
recondate.Value = Format(Date, "dd/mm/yy")
txttime.Value = Time
txtdateonsite.Value = ""
With cboccar
.AddItem "Yes"
.AddItem "No"
End With
txtmiles.Value = ""
txttravel.Value = ""
txtnotes.Value = ""
End Sub
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
OK, I'm a little unclear on exactly what you're asking but I think it's that you want to have the userform shown, have a cell in column C be the active cell and have the textbox ("txtrecol") return the value of column L from the active cell's row. (yes?)

Try including something like this: (after the cell in column C has been activated.)
Code:
Me.txtrecol.Value = ActiveCell.Offset(, 9)
Or - written another way...
Code:
Me.txtrecol.Value = ActiveCell(, 10)
If I'm right about what you want then either one should work.

Hope it helps.

 
Upvote 0
yes thats right, sorry my request wasn't very clear.
it works perfectly, thank you.

just one more thing,

in my user form i have a text box called "txtmiles" and another called "txttravel". I want these 2 text boxes to be unselectable unless "txtrecol" ="yes"

any ideas how i would change the code to have them unselectable unless "yes" is shown in "txtrecol".
"txtrecol" gets its value from column "L" in my database.
 
Upvote 0
Add an if statement something like this

IF me.txtrecol="Yes" then
me.txtmiles.Enabled=False
me.txttravel.Enabled=False
Else
me.txtmiles.Enabled=True
me.txttravel.Enabled=True
End IF
 
Upvote 0
OK, here's a way you can do that.
In vba, go into the userform and select one of those textboxes.
In the properties for that textbox, set the 'Enabled' property to False.
Do the same for the other textbox.

Now, a bit of code for the txtrecol textbox.
Code:
Private Sub txtrecol_Change()
Me.txtmiles.Enabled = Me.txtrecol.Value = "yes"
Me.txttravel.Enabled = Me.txtrecol.Value = "yes"
End Sub

This should be all that's needed. When the userform is shown, the textboxes will be dis-abled by default. The only thing that can enable them is the textbox 'txtrecol' getting a value of 'yes'.

Does that help?
 
Upvote 0
remember to retest the values when the row reference is changed AND when the form is Initialise()d
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,954
Members
448,535
Latest member
alrossman

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