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

Anjinsan5Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
PaulHewsConnect With a Mentor Commented:
>Can you give me an example of how I would go about trapping the enter keypress?

Something like this:  (You would need to adjust for your needs)

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

That's because of the EnterCell handler.  Fix it by setting the textbox to invisible

Private Sub Text1_KeyUp(KeyCode As Integer, Shift As Integer)
    If KeyCode = vbKeyReturn Then
        Text1.Visible = False
        MSFlexGrid1.SetFocus
    End If
End Sub
 
Private Sub MSFlexGrid1_EnterCell()
        Text1.Visible = False
       Text1.Text = Replace(MSFlexGrid1.Text, ".", "")
End Sub

Open in new window

0
 
PaulHewsCommented:
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

0
 
Anjinsan5Author Commented:
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?    
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
PaulHewsCommented:
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
0
 
Anjinsan5Author Commented:
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

0
 
PaulHewsCommented:
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

0
 
Anjinsan5Author Commented:
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?
0
 
PaulHewsCommented:
>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?

0
 
Anjinsan5Author Commented:
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.  
0
 
Anjinsan5Author Commented:
Works better...thanks!  
0
All Courses

From novice to tech pro — start learning today.