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
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
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.

 

Rob Mann

Board Regular
Joined
Jan 10, 2011
Messages
63
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.
 

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,701
Office Version
  1. 2016
Platform
  1. Windows
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
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454

ADVERTISEMENT

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?
 

obiron

Active Member
Joined
Jan 22, 2010
Messages
469
remember to retest the values when the row reference is changed AND when the form is Initialise()d
 

Watch MrExcel Video

Forum statistics

Threads
1,122,552
Messages
5,596,801
Members
414,103
Latest member
imamalidadashzada

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
Top