Keep uppercase format in new inserted row using command button vba

[UNIC0RN]

Hi & Good Day..

I'm new here and I have unsolved issue that I posted in other excel forum and hopely my thread is not category as a cross-posted

http://www.ozgrid.com/forum/forum/help-forums/excel-general/150194-auto-uppercase-in-new-row-using-command-button?t=205762

I need an immediate auto-uppercase once the row added. How can it be done? Looking an advise on it. (err...should I re-post the code? or will get banned if do so?)

*If anyone willing to answer, with respect, please explain it in simple word and simple English because I am not too good. Thanks

 

[ranman256]

you could try:

open excel doc,
open the VBE window: Alt-F11, or ( DEVELOPER tab, VISUAL BASIC button)
in the far left PROJECT window pane,
under the VBAProject (your workbook)
dbl-click the ThisWorkbook object,

PASTE THE FOLLOWING CODE:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
 Target.Value = UCase(Target.Value)
End Sub

then everything you type will convert to upper case.

 

[pbornemeier]

Code must be in the codepage of the sheet in question

Sub Upper()
    Dim rngCell As Range
    For Each rngCell In Range("B9:I9")
        rngCell.Value = UCase(rngCell.Value)
    Next
End Sub

 

[UNIC0RN]

Thanks for the code. I removed my original code and tried your code but its appeared Run-time error '13' Type mismatch, an debung pointed to : Target.Value = UCase(Target.Value)

 

[UNIC0RN]

[h=2]Re: Keep uppercase format in new inserted row using command button vba[/h]

Thanks for the code. I removed my original code and tried your code but its appeared Run-time error '13' Type mismatch, an debung pointed to : Target.Value = UCase(Target.Value)

I used ranman256 code


​

 

[UNIC0RN]

Thanks for the code pbornemeier. I replaced my code to your code and tested but nothing happen

 

[pbornemeier]

If the code is in a worksheet code page then it will only work on that worksheet.
If the code is in a standard module then it will work on the active worksheet.
It works when the code is run manually.

If you call the code in the standard module with a command button it will work.

How did you test it? Add a second row and put Stop in it to see if the sub is being executed.

 

[UNIC0RN]

If the code is in a worksheet code page then it will only work on that worksheet.
- Yes, the code added in Sheet 1

If the code is in a standard module then it will work on the active worksheet. It works when the code is run manually. If you call the code in the standard module with a command button it will work.
- I already tried to move the code in standard module (Insert > Module: right?) and tested but get same result.


How did you test it?
- Previously I put 'Call Upper' in Private Sub CommandButton1 before End Sub row. Then I put the code in the first row and it works but the text not immediately uppercase, its only convert to uppercase in second row -when new row added. Is there any way to make it uppercase immediately?


Add a second row and put Stop in it to see if the sub is being executed.
- I tried to put 'Stop' in second row of the code but debug pointed to it, same result if I put it in any row.

 

[pbornemeier]

Put this code on the codepage of the worksheet in question. it will trigger when any cell on the worksheet is changed. If that change is in the B9:I9 range it will convert each cell the B9:I9 range to uppercase.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngCell As Range
    If Not Intersect(Target, Range("B9:I9")) Is Nothing Then
        For Each rngCell In Range("B9:I9").Cells
            Application.EnableEvents = False
            rngCell = UCase(rngCell)
            Application.EnableEvents = True
        Next
    End If
End Sub

 

[UNIC0RN]

pbornemeier , thank you for the code. I have tested and it's working perfectly