Results 1 to 14 of 14

Edit cell from userform - does not work on integers

Threaded View

  1. #1
    Forum Contributor
    Join Date
    11-10-2009
    Location
    Perth, Australia
    MS-Off Ver
    Excel 2007
    Posts
    549

    Edit cell from userform - does not work on integers

    This userform allows editing of cells via textboxes.
    A character can be added using command buttons.
    It does not work when a cell has integers only. I've tried converting to text using "CStr" but no luck.
    How can I make it work?
    Form code is shown below. Other parts of the code are in the workbook.

    Option Explicit
    
    Private WithEvents MyApplication As Excel.Application
    Public WithEvents cDelegate As clsDelegate
    
    Private TextBoxes As Collection 'For textboxes
    Private mActiveTextBox As MSForms.TextBox 'For textboxes
    
    Dim Buttons() As New clsEventsButtons 'For editing using Command Buttons
    
    'INITIALIZE
    Private Sub UserForm_Initialize()
        Set cDelegate = New clsDelegate
        Call FormCreation(True)
    End Sub
    
    'ACTIVATE
    Private Sub UserForm_Activate()
        ButtonExit.SetFocus
    End Sub
    
    'CREATE FORM
    Private Sub FormCreation(BuildButtons As Boolean)
        Dim cEvents As clsEventsBoxes
        Dim Box As MSForms.TextBox
        Dim BoxTop As Long, BoxHeight As Long, BoxLeft As Long, BoxWidth As Long, BoxGap As Long
        Dim BoxName As String
        Dim cell As Range
        Dim MultiPageHeight As Double
        Dim MyControl As MSForms.Control
        Dim ButtonCount As Integer
        Dim Index As Long
        'Introduction
        Set MyApplication = Application
        BoxHeight = 24: BoxTop = 0: BoxLeft = 0: BoxWidth = 388: BoxGap = 0
        Index = 1
        If TextBoxes Is Nothing Then
            Set TextBoxes = New Collection
        End If
        'Create textboxes
    Dim RangeColumn As Range
    For Each RangeColumn In Selection.Columns 'To change processing by columns (default is by rows)
    For Each cell In RangeColumn.Cells
                If Index > TextBoxes.Count Then
                    Set cEvents = New clsEventsBoxes
                    Set cEvents.cDelegate = cDelegate
                    BoxName = "TextBox" & Index
                    Set Box = Me.TextBoxFrame.Controls.Add("Forms.Textbox.1", BoxName, True)
                    Set cEvents.TextBoxGo = Box
                    TextBoxes.Add cEvents
                Else
                    Set Box = TextBoxes(Index).TextBoxGo
                End If
                With Box
                    .Height = BoxHeight: .top = BoxTop: .Left = BoxLeft: .width = BoxWidth
                    .Font.Size = 12
                    .Text = cell.Formula
    .Text = CStr(.Text)
                    .AutoWordSelect = False
                End With
                Index = Index + 1
                BoxTop = BoxTop + BoxHeight + BoxGap
    Next cell
    Next RangeColumn
        'Remove extra textboxes
        Do While TextBoxes.Count > Index
            TextBoxes.Remove TextBoxes.Count
        Loop
        'Create Command Button objects
        If BuildButtons Then
            ButtonCount = 0
            For Each MyControl In Me.Controls
                If TypeName(MyControl) = "CommandButton" Then
                    ButtonCount = ButtonCount + 1
                    ReDim Preserve Buttons(1 To ButtonCount)
                    Set Buttons(ButtonCount).ButtonGroup = MyControl
                End If
            Next MyControl
        End If
        'Populate Command Buttons
        Application.Run "FormButtonsCharacters" 'Application.Run since Private Sub
    End Sub
    
    'RECEIVING EVENT
    Private Sub cDelegate_TextBoxGoChanged(TextBoxGo As MSForms.TextBox)
        Set mActiveTextBox = TextBoxGo
    End Sub
    
    'INSERT CHARACTERS
    Public Sub InsertCharacter(CharacterType As String) 'Public since called  from Class Module
        Dim TextBox As MSForms.TextBox
        Dim cell As Range
        Dim CursorLocation As Long
        Dim Index As Long
        Index = 1
        For Each cell In MyApplication.Selection
            If Not IsEmpty(cell) Then
                If TextBoxes(Index).TextBoxGo.Name = mActiveTextBox.Name Then
                    With mActiveTextBox
                        CursorLocation = .SelStart
                            cell.Characters(.SelStart + 1, 0).Insert CharacterType
                            mActiveTextBox.Text = Left(.Text, .SelStart) & CharacterType & Mid(.Text, .SelStart + 1)
                            .SelStart = CursorLocation + Len(CharacterType)
                            .SetFocus 'Prevents the cursor from disappearing
                    End With
                End If
            Index = Index + 1
            End If
            Next
    End Sub
    
    'EXIT
    Private Sub ButtonExit_Click()
        Unload Me
    End Sub
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Cell lookup and edit via userform
    By ste2610 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-27-2016, 07:21 AM
  2. [SOLVED] Userform text box to display and edit a single cell only
    By Nitefox in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 5
    Last Post: 11-25-2013, 08:50 AM
  3. VBA Userform - Restrict Text Box to integers between 18 - 80
    By kamelkid2 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-06-2013, 03:38 PM
  4. Vlookup doesn't work until i edit(but not change) the lookup cell
    By Confused in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-08-2005, 05:25 AM
  5. A list of Consecutive Integers, can I search for missing integers
    By CM in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 06:05 PM
  6. A list of Consecutive Integers, can I search for missing integers
    By Harlan Grove in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 09-06-2005, 05:05 AM
  7. A list of Consecutive Integers, can I search for missing integers
    By CM in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1