Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
column of entry for textbox value
Hi I am having trouble with my userform. I have 5 controls on the form. a calendar (calendar1) 2 combos (cbosite & cbometerno) 1 textbox (txtreading) 1 command button (cmdaddrecord) The user selects a value in the calendar and this is added to the first available row in the sheet. cbosite has a selection of all the sheet names in the workbook. The selection here decides which sheet the calendars input and the txtreading is printed to. up to now I have this part working. I choose a date, select a sheet name from cbosite and click cmdaddrecord. This activates the relevant sheet and enters the date in the first blank row in column a as the code asks. The part I dont know how to do is to take the row number from where calendar 1 has just entered and put it on one side to use as the row number to enter txtreading. To find the column for txtreading I need to some how cross reference the choice in cbometerno which will be found anywhere between B1:K1 and take the column letter. Once I have this I know that txtreading should be entered in row x and column y. I have tried to explain that as well as I can but I have attached a copy of the sheet to this post if you want to have a look. Thanks for your time having a look. Rob +-------------------------------------------------------------------+ |Filename: excelproject.zip | |Download: http://www.excelforum.com/attachment.php?postid=3619 | +-------------------------------------------------------------------+ -- robhargreaves ------------------------------------------------------------------------ robhargreaves's Profile: http://www.excelforum.com/member.php...o&userid=25519 View this thread: http://www.excelforum.com/showthread...hreadid=389584 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
column of entry for textbox value
If I understand you
x = row number y = column number Than use: Cells(x,y) instead of Range("A1") make sure x & y are Dim as Long. general statement include workbook, sheet, and range designater's WorkBooks("MyBook").Sheets("MySheet").Cells(x,y) = "My Text" -- steveB Remove "AYN" from email to respond "robhargreaves" wrote in message news:robhargreaves.1smlib_1122127507.0083@excelfor um-nospam.com... Hi I am having trouble with my userform. I have 5 controls on the form. a calendar (calendar1) 2 combos (cbosite & cbometerno) 1 textbox (txtreading) 1 command button (cmdaddrecord) The user selects a value in the calendar and this is added to the first available row in the sheet. cbosite has a selection of all the sheet names in the workbook. The selection here decides which sheet the calendars input and the txtreading is printed to. up to now I have this part working. I choose a date, select a sheet name from cbosite and click cmdaddrecord. This activates the relevant sheet and enters the date in the first blank row in column a as the code asks. The part I dont know how to do is to take the row number from where calendar 1 has just entered and put it on one side to use as the row number to enter txtreading. To find the column for txtreading I need to some how cross reference the choice in cbometerno which will be found anywhere between B1:K1 and take the column letter. Once I have this I know that txtreading should be entered in row x and column y. I have tried to explain that as well as I can but I have attached a copy of the sheet to this post if you want to have a look. Thanks for your time having a look. Rob +-------------------------------------------------------------------+ |Filename: excelproject.zip | |Download: http://www.excelforum.com/attachment.php?postid=3619 | +-------------------------------------------------------------------+ -- robhargreaves ------------------------------------------------------------------------ robhargreaves's Profile: http://www.excelforum.com/member.php...o&userid=25519 View this thread: http://www.excelforum.com/showthread...hreadid=389584 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
column of entry for textbox value
Thanks steve I have got some code like this but it doesnt work. Can you comment? Private Sub cmdaddrecord_Click() Dim R As Range, C As Integer Set R = Range("B1:K1").Find(cbometerno) ActiveWorkbook.Sheets(Mid(cbosite.Text, 2, Len(cbosite.Text) - 2)).Activate Range("A14").End(xlDown).Offset(1, 0).Value = Calendar1.Value 'if a match is found If Not R Is Nothing Then C = R.Column 'which will return the column number Cells(15, C).Value = txtreading.Value Unload Me End Sub -- robhargreaves ------------------------------------------------------------------------ robhargreaves's Profile: http://www.excelforum.com/member.php...o&userid=25519 View this thread: http://www.excelforum.com/showthread...hreadid=389584 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
column of entry for textbox value
Looks pretty straight forward.
put a breakpoint at the line Set R and run the form. This should get you back to the code and you can check variable values as you step through it. instead of cbosite.text try cbosite.value this part may be the major offender If Not R Is Nothing Then C = R.Column 'which will return the column number Cells(15, C).Value = txtreading.Value change it to If Not R Is Nothing Then C = R.Column 'which will return the column number Cells(15, C).Value = txtreading.Value End If Let me know if this helps... -- steveB Remove "AYN" from email to respond "robhargreaves" wrote in message news:robhargreaves.1smtub_1122138308.5001@excelfor um-nospam.com... Thanks steve I have got some code like this but it doesnt work. Can you comment? Private Sub cmdaddrecord_Click() Dim R As Range, C As Integer Set R = Range("B1:K1").Find(cbometerno) ActiveWorkbook.Sheets(Mid(cbosite.Text, 2, Len(cbosite.Text) - 2)).Activate Range("A14").End(xlDown).Offset(1, 0).Value = Calendar1.Value 'if a match is found If Not R Is Nothing Then C = R.Column 'which will return the column number Cells(15, C).Value = txtreading.Value Unload Me End Sub -- robhargreaves ------------------------------------------------------------------------ robhargreaves's Profile: http://www.excelforum.com/member.php...o&userid=25519 View this thread: http://www.excelforum.com/showthread...hreadid=389584 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Take action after entry in a textbox (or after tabbing out of/into it) | Excel Programming | |||
data entry to next blank TextBox | Excel Programming | |||
Validate textbox entry | Excel Programming | |||
Validate Textbox entry | Excel Programming | |||
Validating Entry into Textbox | Excel Programming |