asked on

Trouble keeping textbox aligned with MSFlexgrid cell when editing...

I have a Form with an MSFlexgrid, and a textbox used for editing the MSFlexgrid. My flexgrid has 2 columns, and I have coded each column to format a value differently when the user enters a value. My current code works great, except now I need to make it easier for a user to edit their value in case they change their mind. Essentially, I need to place a caret in the textbox and allow the user to change anywhere from a single character to a whole selection of characters in the given cell. Right now their is no indication of what character is being changed because the textbox has taken on the msflexgrid's properties and dropped the caret. When I set the textbox to visible, all that does is send the textbox way off to the left or right of the MSFlexgrid. Below is my code for the flexgrid and textbox events. Any ideas?

Private Sub MSFlexGrid1_EnterCell()
       Text1.Text = Replace(MSFlexGrid1.Text, ".", "")
End Sub
 
Private Sub MSFlexGrid1_KeyDown(KeyCode As Integer, Shift As Integer)
  If KeyCode = vbKeyDelete Then
        MSFlexGrid1.Text = ""
        End If
End Sub
 
Private Sub MSFlexGrid1_KeyPress(keyascii As Integer)
 
With Text1
     Select Case keyascii
        Case vbKeyReturn, vbKeyTab
     '  move to next cell.
            If MSFlexGrid1.Col + 1 <= MSFlexGrid1.Cols - 1 Then
                MSFlexGrid1.Col = MSFlexGrid1.Col + 1
      Else
                If MSFlexGrid1.Row + 1 <= MSFlexGrid1.Rows - 1 Then
                    MSFlexGrid1.Row = MSFlexGrid1.Row + 1
                    MSFlexGrid1.Col = 1
                Else
                    MSFlexGrid1.Row = 0
                   MSFlexGrid1.Col = 1
                End If
          End If
            Case 8
                If Not Text1.Text = "" Then
                  Text1.Text = Left(Text1.Text, Len(Text1.Text) - 1)
                End If
            Case 9 ' Tab
                If MSFlexGrid1.Col + 1 = MSFlexGrid1.Cols Then
                MSFlexGrid1.Col = 0
                    If MSFlexGrid1.Row + 1 = MSFlexGrid1.Rows Then
                      MSFlexGrid1.Row = 0
                    Else
                        MSFlexGrid1.Row = MSFlexGrid1.Row + 1
                    End If
                Else
                    MSFlexGrid1.Col = MSFlexGrid1.Col + 1
             End If
        Case 48 To 57 'numbers only
            If InStr(1, Text1.Text, ".", vbTextCompare) > 0 Then
                    If Len(Text1.Text) >= 6 Then Exit Sub
                    Else
                   If Len(Text1.Text) >= 7 Then Exit Sub
               End If
            Text1.Text = Text1.Text & Chr$(keyascii)
     Case Else
        End Select
    End With
End Sub
 
Private Sub Text1_Change()
Dim position
With MSFlexGrid1
    If .ColSel = 1 Then
    .TextMatrix(.RowSel, .ColSel) = Col1todecimal(Text1.Text)
    End If
    If .ColSel = 2 Then
    .TextMatrix(.RowSel, .ColSel) = Col2todecimal(Text1.Text)
    End If
End With
End Sub

Open in new window

PaulHews

I've used this code successfully in the past.

Option Explicit
 
 
Private Sub MSFlexGrid1_Click()
    MSFlexGrid1.SetFocus
    Text1.Visible = False
    Text1 = ""
End Sub
 
 
 
Private Sub MSFlexGrid1_GotFocus()
    Text1.Visible = False
    Text1 = ""
    Text1.ForeColor = vbBlack
    Text1.BackColor = vbWhite
End Sub
 
Private Sub MSFlexGrid1_KeyPress(KeyAscii As Integer)
    'MSFlexGrid1.SetFocus
    If MSFlexGrid1.CellBackColor <> vbBlack Then
        Text1.ForeColor = MSFlexGrid1.CellForeColor
        Text1.BackColor = MSFlexGrid1.CellBackColor
    End If
    Text1.Left = MSFlexGrid1.CellLeft + MSFlexGrid1.Left
    Text1.Top = MSFlexGrid1.CellTop + MSFlexGrid1.Top
    Text1.Width = MSFlexGrid1.CellWidth
    Text1.Height = MSFlexGrid1.CellHeight
    Text1.Visible = True
    Text1.SetFocus
End Sub
 
Private Sub Text1_Change()
If Text1 <> "" Then
MSFlexGrid1.Text = Text1
End If
 
End Sub
 
 
   
 
Private Sub Text1_KeyDown(KeyCode As Integer, Shift As Integer)
 Select Case KeyCode
    Case vbKeyRight
        MSFlexGrid1.Col = MSFlexGrid1.Col + 1
        MSFlexGrid1.SetFocus
    Case vbKeyLeft
        MSFlexGrid1.Col = MSFlexGrid1.Col - 1
        MSFlexGrid1.SetFocus
    Case vbKeyDown
        MSFlexGrid1.Row = MSFlexGrid1.Row + 1
        MSFlexGrid1.SetFocus
    Case vbKeyUp
        MSFlexGrid1.Row = MSFlexGrid1.Row - 1
        MSFlexGrid1.SetFocus
    End Select
    
End Sub

Open in new window

Anjinsan5

ASKER

PaulHews,

Thanks for the suggestion. I used your events in place of mine and had the same issue with the textbox appearing off to the top left of the form. My main concern though is the uncertainty over whether I will be able to add the new code I am shooting for and still keep my existing code for formatting the columns. Any suggestions based on my code that could possibly fix the problem?

PaulHews

Is your flexgrid located on a container control, such as a frame or picturebox? If so, you will have to add their contribution to the location of the textbox:

Text1.Left = Frame1.Left + MSFlexGrid1.CellLeft + MSFlexGrid1.Left
Text1.Top = Frame1.Top + MSFlexGrid1.CellTop + MSFlexGrid1.Top

Anjinsan5

ASKER

Yes I do have a frame around the msflexgrid. I added the code below to my code based on your suggestion...I took out the + msflexgrid1.height, width, left, and top at the end because they were enlarging each cell to a size bigger than the frame when I double clicked the cell. However, I still have the same issue where everytime I double click a cell the cell becomes enlarged bigger than the frame...

Private Sub MSFlexGrid1_DblClick()
    Text1.Height = frmInput.Height + MSFlexGrid1.CellHeight
    Text1.Width = frmInput.Width + MSFlexGrid1.CellWidth
    Text1.Left = frmInput.Left + MSFlexGrid1.CellLeft
    Text1.Top = frmInput.Top + MSFlexGrid1.CellTop
    Text1.Visible = True
    Text1.SetFocus
End Sub

Open in new window

PaulHews

Wow, if you make the textbox wider than the frame, no wonder it's screwing up... Try this:

Private Sub MSFlexGrid1_DblClick()
    Text1.Height = MSFlexGrid1.CellHeight
    Text1.Width = MSFlexGrid1.CellWidth
    Text1.Left = frmInput.Left + MSFlexGrid1.Left + MSFlexGrid1.CellLeft
    Text1.Top = frmInput.Top + MSFlexGrid1.Top + MSFlexGrid1.CellTop
    Text1.Visible = True
    Text1.ZOrder
    Text1.SetFocus
End Sub

Open in new window

Anjinsan5

ASKER

Ok, somewhat have it working now with the following code:
Private Sub MSFlexGrid1_DblClick()

With MSFlexGrid1
Text1.Height = .CellHeight
Text1.Width = .CellWidth
Text1.Left = .CellLeft + .Left + frmInput.Left
Text1.Top = .CellTop + .Top + frmInput.Top
Text1.SelStart = 0
Text1.SelLength = Len(Text1.Text)
Text1.Visible = True
End With
End Sub

Except now you have to click another cell to escape the existing cell and it shows the value of the old cell in the new cell temporarily when clicked...Any ideas?

PaulHews

>Except now you have to click another cell to escape the existing cell

You could trap the enter keypress to remove the textbox.

>and it shows the value of the old cell in the new cell temporarily when clicked...Any ideas?

I don't understand this?

Anjinsan5

ASKER

Can you give me an example of how I would go about trapping the enter keypress?

When you navigate between cells, the value from the previous cell sometimes sticks momentarily in the new cell that has focus.

ASKER CERTIFIED SOLUTION

PaulHews

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anjinsan5

ASKER

Works better...thanks!