Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Show Current Row Columns UserForm TextBox Controls

I have a spreadsheet that has a userform attached to it with text fields that enter additional data regarding each record. The userform takes these additional 5 fields and inserts them back into the spreadsheet, the final information to be exported back into Access. ( There are of course other ways to do this, but this is how my supervisor wants it done!)

My problem is that now that I can scroll through the spreadsheet without closing the userform, the userform doesnt show the current row's information as I scroll or change rows on the spreadsheet. It will only show the information that was in the row that it was on before i switched back to spreadsheet.

I would like for the userform to show the information in each row as I change rows in the spreadsheet.


Post your answer or comment

comments powered by Disqus
I'm using Excel 2003, can u plz help me out that how to highlight current row
& column with a color automatically to identify my position. Though excel
help us by highlighting column & row number, but i want whole column & row to
be highlighten to better finding my cell position and contents. So When I
change my cell position, the highlighted row & column move appropriately.

I'm using Excel 2003, my question is how to highlight current row & column
with a color automatically, to identify my position. Though excel help us by
highlighting column & row number, but i want whole column & row to be
highlighten to better finding my cell position and all the contents in
appropriate row & column. So When I change my cell position, the highlighted
row & column move appropriately.

I'm using Excel 2003, can u plz help me out that how to highlight current row
& column with a color automatically to identify my position. Though excel
help us by highlighting column & row number, but i want whole column & row to
be highlighten to better finding my cell position and contents. So When I
change my cell position, the highlighted row & column move appropriately.

I have the following code that is not working properly. When the form loads it always shows data from the previous time the form loaded. I searched and found the following code which doesn't seem to be working correctly. I thought by unloading the userform the values of the textboxes would "reset". Not happening.


	VB:
	
 CommandButton1_Click() 
     
    Dim cRow 
    cRow = ActiveCell.Row ' remember the current row
     
    Cells(cRow, Range("Column_Type_Of_Ride").Column).ClearContents 
     
    Unload Erase_Ride_Form 
    Erase_Ride_Form.Hide 
     
End Sub 
 
Private Sub CommandButton2_Click() 
     
    Unload Erase_Ride_Form 
    Erase_Ride_Form.Hide 
     
End Sub 
 
 
Private Sub UserForm_Initialize() 
     
    Dim cRow 
    cRow = ActiveCell.Row ' remember the current row
     
    TextBox1 = Cells(cRow, Range("column_date").Column).Value 
    TextBox2 = Cells(cRow, Range("Column_Solo_Group_Name").Column).Value 
    TextBox3 = Cells(cRow, Range("column_distance").Column).Value 
    TextBox4 = Cells(cRow, Range("column_duration").Column).Value 
     
    TextBox4 = Format(TextBox4, "hh:mm:ss") 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi,

Would like to ask how can I copy the last row number into textbox ?
For Range A1, the last row number is 10 , the textbox will show 11 when open a userform, it's mean that it will auto count the number.
Or the last row number is 50, the textbox will show 51 when user open a userform.

Thanks.
Jimmy

I voted for the suggestion of a crosshair highlight so the current cell is
more visible.
A twist to make this even better is to KEEP the highlight active even when
the focus (in Windows) goes to another program. in the newest Excel, the row
# and column letter are highlighted for the current cell. the preferred
Excel would highlight the ENTIRE row and column (crosshair) and when I click
on another running program window, the crosshair remains in Excel. Currently
the highlighted row/column (that exists now) disappears when the window focus
changes to another program. try it and you'll see what i mean. I want to
work with my other program using the current cell's data.
RyanV.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

Hi there, First I'm a total newbie so sorry if this is too obvious but I'm working with VBA and excel 2003 and I want to change the value of a cell. Sounds simple but.. I've cobbled together code which allows me to use a drop down box to select a name from column B and can even select the corect row and clear all the data on it. But I can't then enter data in certain cells on that row. Here's my code:

FindString = CBName.Value
    If Trim(FindString) <> "" Then
        With Sheets("Main").Range("B:B")
            Set Rng = .Find(What:=FindString, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
            If Not Rng Is Nothing Then
            ' clear row contents
                Application.Goto Rng, True
                ActiveCell.EntireRow.Clear
            ' replace with zzz etc
            ' this is the problem here
            Cells(ActiveCell) = "zzz"
            Cells(ActiveCell + 2) = "z"
            
            Else
                MsgBox "Nothing found"
            End If
        End With
    End If
I've emptied the row and now want to add "zzz" to (current row, column B) as well as "01/01/2099" to other cells on the same row.

Thanks for taking pity on a newbie!
Ps thanks for putting me straight about code. Got it!

This may be a trivial task, but I am hoping someone can help me out.

I need to create an array that contains:
Current Row, Column A Value

I need to read the sheet until no more values are found.
I then need to figure out how to reference the values in the array.

I really apprecaite any help and code samples that can be supplied.

thanks

k

Example 20120620 version 01.xls2012-06-20 15-37-11.jpg

I am trying to create a very simple and easy to use case management system or case management overview using excel.
My colleges use simple row-by-row lists (see attached) for entering basic information regarding our work. Every process or “task” have the same basic information requirements such as case number, external case no, start date and so on (see attached file).
2012-06-20 15-37-11.jpg

It is normal for this excel file to have up to and beyond 50 rows/50 cases active during all times and my thought is this;Simplify the Mastersheet (the first sheet) in the workbook to gain a better overview. This I want to do by hiding certain columns on MastersheetOn sheet “Details” create a “Userform-look” that can show all information in the entire row, including the hidden cells on Mastersheet.
Step one on sheet “Details” have been done; I have a cell that generates a dropdown list of the case number (called TMJ). Once the user has selected a TMJ number the textboxes control source should change to correspond to that specific row selected and offset once, twice, three times… and so on until all columns are set to corresponding textbox.

I think it’s a simple idea and the main point here is to create a top-view in Mastersheet and a detailed view in Details-sheet.

Step 2 in this thought of mine is to be able to generate a sheet with prioritized filtering of all cases. If I want to do this I will need a column that I can set to “High, Normal and Low”, and this I believe I can manage but if anyone has any clever ideas to share on how to really hit this idea of mine off then please share it!

Best regards
Kaherdin
Sweden

How can I have my textbox in a userform accept blank entries. Right now I have to enter a zeros for textboxes I want to have no amounts. Please here my userform code below. Please help.


	VB:
	
 CommandButton1_Click() 
    Dim arr(11) 
    For i = 0 To 11 
        arr(i) = Me.Controls("textbox" & i + 1).Value * 1 
    Next i 
    If WorksheetFunction.Sum(arr)  ActiveCell.Offset(0, -2) Then 
        TextBox1.SetFocus 
        MsgBox "Doesn't add up to requested amount." & vbCrLf & "Current Total: " & WorksheetFunction.Sum(arr) _ 
        & vbCrLf & "Required: " & ActiveCell.Offset(0, -2).Value 
    Else 
        Sheets("Prof Monthly spread").Cells(ActiveCell.Row, 1).Resize(1, 12) = arr 
        Unload Me 
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Greetings,

I am trying to populate the first 12 columns in the last row of the active sheet from 12 UserForm TextBoxes (TB5 thru TB16). Here is the code:

	VB:
	
 Range("A65536").End(xlUp) 
    For i = 1 To 12 
        .Offset(0, i - 1).Value = Me.Controls("TB" & i + 4).Value 
        MsgBox "Cycle " & i 
    Next i 
End With 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The MsgBox returns a "Cycle 13" only once. The Row has the first column filled but none of the rest. I am trying to load TB5 thru TB16 into columns A thru L of the last row (the last row was copied down to get the formatting and this code was supposed to over write the copied data and leave the formatting). But only TB5 loads into Column A!!!

Anyone see anything wrong with this code?

Or if anyone has any sugestions as to how to make it work?

Any help is greatly appreciated.

-Minitman

I have a question on the userform spreadsheet control. I have a multipage userform. One of the pages contains a spreadsheet control - the spreadsheet is 8 columns and 30 rows - maximum. I am having difficulty in applying code to this spreadsheet control which will grab what is in the spreadsheet and put it on the "record" sheet of the userform. The code that I use for textboxes is (as an example)
XferReorgForm.ReorgMgrGID.Text = Range("T2").Text
XferReorgForm.ReorgMgrName.Text = Range("U2").Text
XferReorgForm.ReorgMgrPosNum.Text = Range("V2").Text

This code is put in the Load routine of the userform.

Is there a way to 'grab' the entire spreadsheet control and 'paste' it to the record sheet, and likewise, load it back into the userform?

Thanks for your help.

I'd like to password protect some features of a spreadsheet, so to take
advantage of the PasswordChar Property of a Textbox control, I created
a small userform with only a text box on it. My intention was to
replace the InputBox function since it doesn't allow character
protection. Unfortunately, with no other controls on the form it
appears that I can't get the exit event to fire without closing the
userform...

If I put another text box on the userform, the exit event fires just
fine when I tab out of the first control.

Am I missing something?

Here are the applicable code fragments
'
' From a UserForm called PSSDataForm
'
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As
Integer)
Load PasswordDataForm
PasswordDataForm.Show
End Sub

'
' From PasswordDataForm
' PasswordBox is the textbox control where I enter the password
'
Sub PasswordBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If PasswordBox.Value = ClosurePassword Then
Unload PSSDataForm
Unload PasswordDataForm
Else
'
' Do other stuff here
'
End If
End Sub

Excel 2002, all current service packs applied, runnning on XP Pro SP1.
(Don't start - its my work laptop and we haven't rolled out SP2 yet...)

I have a spreadsheet which I want to use for sales recording/stock control, within it I have a userform with product references and textboxes so a user can enter the quantity sold of certain products. Data will go to two worksheets (WS1 as a historical record of sales, and WS2 as a running total). However with my current setup my userform will only replace the number in its assigned cell in WS2. What I want to do is make the userform add to the cell in WS2 so I can have a running total. Im hoping it's just a simple problem with a simple answer, here's my current code;

Private Sub
cmdAdd_Click()
    Dim iRow1 As Long
    Dim iRow2 As Long
    Dim WS1 As Worksheet, WS2 As Worksheet
    Set WS1 = Worksheets("RECORD")
    Set WS2 = Worksheets("COMPONENTS")
     
     'find first empty row in database
    iRow2 = WS2.Cells(Rows.Count, 2) _
    .End(xlUp).Offset(1, 0).Row
     
     'find first empty row in database
    iRow1 = WS1.Cells(Rows.Count, 2) _
    .End(xlUp).Offset(1, 0).Row



'copy the data to the database "RECORD"

WS1.Cells(iRow1, 1).Value = Me.TXTDATE
WS1.Cells(iRow1, 2).Value = "DISPATCH"
WS1.Cells(iRow1, 3).Value = "DOUBLE WIDTH 2.7M"
WS1.Cells(iRow1, 4).Value = Me.DW32.Value
WS1.Cells(iRow1, 5).Value = Me.DW37.Value
WS1.Cells(iRow1, 6).Value = Me.DW42.Value
WS1.Cells(iRow1, 7).Value = Me.DW47.Value
WS1.Cells(iRow1, 8).Value = Me.DW52.Value
WS1.Cells(iRow1, 9).Value = Me.DW57.Value
WS1.Cells(iRow1, 10).Value = Me.DW62.Value
WS1.Cells(iRow1, 11).Value = Me.DW67.Value
WS1.Cells(iRow1, 12).Value = Me.DW72.Value
WS1.Cells(iRow1, 13).Value = Me.DW77.Value
WS1.Cells(iRow1, 14).Value = Me.DW82.Value
WS1.Cells(iRow1, 15).Value = Me.DW87.Value
WS1.Cells(iRow1, 16).Value = Me.DW92.Value
WS1.Cells(iRow1, 17).Value = Me.DW97.Value
WS1.Cells(iRow1, 18).Value = Me.DW102.Value
WS1.Cells(iRow1, 19).Value = Me.DW107.Value
WS1.Cells(iRow1, 20).Value = Me.DW112.Value
WS1.Cells(iRow1, 21).Value = Me.DW117.Value
WS1.Cells(iRow1, 22).Value = Me.DW122.Value
WS1.Cells(iRow1, 23).Value = Me.DW127.Value
WS1.Cells(iRow1, 24).Value = Me.DW132.Value
WS1.Cells(iRow1, 25).Value = Me.DW137.Value
WS1.Cells(iRow1, 26).Value = Me.DW142.Value


'copy the data to the database "COMPONENTS"

WS2.Range("D6").Value = Me.DW32.Value
WS2.Range("E6").Value = Me.DW37.Value
WS2.Range("F6").Value = Me.DW42.Value
WS2.Range("G6").Value = Me.DW47.Value
WS2.Range("H6").Value = Me.DW52.Value
WS2.Range("I6").Value = Me.DW57.Value
WS2.Range("J6").Value = Me.DW62.Value
WS2.Range("K6").Value = Me.DW67.Value
WS2.Range("L6").Value = Me.DW72.Value
WS2.Range("M6").Value = Me.DW77.Value
WS2.Range("N6").Value = Me.DW82.Value
WS2.Range("O6").Value = Me.DW87.Value
WS2.Range("P6").Value = Me.DW92.Value
WS2.Range("Q6").Value = Me.DW97.Value
WS2.Range("R6").Value = Me.DW102.Value
WS2.Range("S6").Value = Me.DW107.Value
WS2.Range("T6").Value = Me.DW112.Value
WS2.Range("U6").Value = Me.DW117.Value
WS2.Range("V6").Value = Me.DW122.Value
WS2.Range("W6").Value = Me.DW127.Value
WS2.Range("X6").Value = Me.DW132.Value
WS2.Range("Y6").Value = Me.DW137.Value
WS2.Range("Z6").Value = Me.DW142.Value

'clear the data
Me.TXTDATE.Value = ""
Me.DW32.Value = ""
Me.DW37.Value = ""
Me.DW42.Value = ""
Me.DW47.Value = ""
Me.DW52.Value = ""
Me.DW57.Value = ""
Me.DW62.Value = ""
Me.DW67.Value = ""
Me.DW72.Value = ""
Me.DW77.Value = ""
Me.DW82.Value = ""
Me.DW87.Value = ""
Me.DW92.Value = ""
Me.DW97.Value = ""
Me.DW102.Value = ""
Me.DW107.Value = ""
Me.DW112.Value = ""

Me.DW117.Value = ""
Me.DW122.Value = ""
Me.DW127.Value = ""
Me.DW132.Value = ""
Me.DW137.Value = ""
Me.DW142.Value = ""

Me.DW142.SetFocus

End Sub
Any help will be really appreciated, thanks.

Hi, all

i have a problem i'm making a little program with Excel Vba i want to show showing 6 cells in 6 textbox's but here's the hard part i got 3 userform for this in userform1 (already working) it shows the cell rows and has 3 buttons Add, Open and delete with "add" it opens userform2 (already working) and let you make new problem calls, when i press delete it delete the selected row from userform1 but now the button "open" has to open the selected row in userform3 and has to past every cell in a different textbox and be saved with a button "save" if ready

so please help me

with kind regards

Kaspar vermaat

PS: sorry i can't post pictures gives a error

in VBA, how can I find my current Row and Column cell address? I want to be able to , for example, first find which cell I am (let's say I am at Row 5 Col 4). then I want to go down 3 Rows (so Row 5 + 3 = Row 8)

What in VBA is easiest to do?

I have a text box on a userform, when the form is opened i need the text box to display the text of column 1 of the active row,

Any ideas?

Hi

Please help.

Does anyone know a code which will highlight the current column and row in a colour (Say Yellow) and will move to whichever cell is selected.

Thanks

Seymour butts

Hi all,

I am trying to lock combo and check boxes in place such that when I hide/unhide certain rows/columns as needed, they do not show up in a different location. The way I have been trying to implement this is that I have been hiding the boxes first and then hiding the rows/columns. I have tried a few troubleshooting suggestions I have found on the internet, such as making sure the entire box is within the borders of a cell and locking it to that cell, but nothing seems to work. Some boxes stay in place while others relocate themselves after a few actions of hiding/unhiding. Any suggestions? Thanks in advance for any and all help.

Sincerely,
Mike

This a new question that arised from my earlier posting in http://www.ozgrid.com/forum/showthread.php?t=86050

I have the following working code below, but am having trouble finding coding examples to select specific cells from the selected row (that was found by selecting a ComboBox value)and update TextBoxes with those individual values after the UserForm has been initialized (the bold "GREEN" comment in the code below). I have been able to find plenty of references to update TextBox values to Cells, but that doesn't do me much good in this application since the User needs to verify the old data in these cells before updating them using the UserForm TextBoxes.

I was toying around with several different variations of code (none of which worked properly), so I left it out for clarity of my working code. I'll post up this non-working code as needed, because I really wanted a fresh answer...not what I was trying to do. The attached file should be sufficient to see what's going on, but if not please ask questions.

thanks, ~Dan


	VB:
	
 UserForm_Initialize() 
     
     'Add list entries to the combo boxes. The value of each
     'box matches the existing SR Information spreadsheet entries
     
    With Sheets("SR Information") 
        .Range("A2", .Cells(Rows.Count, "A").End(xlUp)).Name = "MyRange" 
    End With 
     
    SRnumber.RowSource = "MyRange" 
     
     ' Set initial values to TextBoxes -not needed, just for testing-
     '    NBdate.Text = "Test NBD SR Number"
     '    ApartNumber.Text = "Test AP SR Number"
     '    UPdetails.Text = "Test UD SR Number"
     
End Sub 
 
 
Private Sub SRnumber_Change() 
     
    Dim ServiceRequestNumber As String 
    Dim c As Range 
    Dim rngG As Range 
    Sheets("SR Information").Select 
     
    With Selection 
         
         'Find value in ComboBox, and select row
         
        ServiceRequestNumber = SRnumber.Value 
         
        For Each c In Intersect(ActiveSheet.UsedRange, Columns("a")) 
            If c = ServiceRequestNumber Then 
                If rngG Is Nothing Then Set rngG = c.EntireRow 
                Set rngG = Union(rngG, c.EntireRow) 
            End If 
        Next c 
        rngG.Select 
         '
        [B][COLOR="Red"] ' >Insert Code Here?<  Update other TextBoxes with information contained in specific cells on the
selected row[/COLOR][/B]
         '
    End With 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Overview:
I am creating a userform with a textbox that I would like to link to a range of cells on a worksheet.

Use:
The user will make a selection from a listbox that corresponds to a category column on the worksheet. The macro will then find all of the rows that contain the user selection in their category column, and will populate / link the text of the textbox to these rows.

Example:
The listbox contains a list of car manufacturers. The user selects BMW from the listbox. The macro sorts through the category column and finds all rows that have BMW in the category column. The textbox is then populated with the information from columns A through D for each row that contains BMW in the category column.

Thanks in advance for all of your help,

Steve

I need to test the value of several userform textboxes and I'm currently using the code Code:
 where Ctrl is declared as a Control.

So far, so good. However, I've noticed that the order in which the controls are tested doesn't correspond with my tab order (suspect it may be the order they were created).

Other than re-creating my userform in the order I wish the controls to be tested, is there a method for specifying the order they should be tested?

Many thanks

Using Excel 2000

I have created a report sheet that must be filled in on a monthly
basis. There is one column per month. One of the cell is a comment box.
As columns will be hidden/displayed at each month change and for
presentation purposes, I have added a textbox control on the sheet (
not moved nor resized ).

The linked cell reference of the textbox comes from a dynamic name that
returns the reference of comment cell of the reporting month.

I have created the following local named range
comment = OFFSET('Sheet1'!$A$1,0,'Sheet1'!mth_offset)

In the linked cell property of the text box
linked cell = 'Sheet1'!comment

mth_offset is another local named range linked to a spinbutton. As I
spin up and down, the text box correctly displays the content of the
cells in row 1, as you would expect from the 'comment' formula.

The strange thing is that if as soon as I edit the content of the text
box, the dynamic link stops working, i.e. the textbox no longer updates
its content as I spin up/.down.

I have found a workaround: in the spin button change event procedure, I
add the code: "TextBox1.LinkedCell = TextBox1.LinkedCell". With that
the text box updates correctly.

Has anyone faced this problem ?
Is there a workaround that does not rely on code ? - getting users to
activate macros can be a pain.

Thanks for your help.
Regards

Hi.
I am great in excel but not so great in macros

I am creating a rperot that has "this year", "plan", and "last year"
columns. I want to be able to hide and show specific columns like
"this year" by pressing a "hide ty" button and once it is hidden, have
that same button now say "show this year" and then show this year
columns.

I have created two button controled macros to do the above but I want
to only have one button that toggles and the text changes from hide to
show.

I have an example of this that I can send to someone.

thank you for your help...I am a rookie!
Sub Hide_TY()
Range("F:F,I:I").Select
Range("I1").Activate
Selection.EntireColumn.Hidden = True
End Sub

Sub unhide_TY()
Range("E1:G1,H1:J1").Select
Range("H1").Activate
Selection.EntireColumn.Hidden = False
Range("F10").Select
End Sub


No luck finding an answer? You could always try Google.