Trigger a Worksheet Change event from a DataForm

[mdmackillop]

Some assistance required here!
http://vbaexpress.com/forum/showthread.php?t=10100

[Charlize]

You could try this one. Macro's are paused when a dataform is active so nothing can be triggered (according to vba helpfile). I came up with this. Macro to call dataform and after the input do the things that you want to do.
Sub Input_Form()
' content of column A to change in uppercase
Dim content As String
' number of rows
Dim lrow As Long
' a loop counter
Dim counter As Long
' the rowposition
Dim pos As Long
' setting the alerts off
Application.DisplayAlerts = False
' headtitles in A1:A2
Sheets(1).Range("A1:B1").Select
' show the dataform
Sheets(1).ShowDataForm
' setting the alerts back on
Application.DisplayAlerts = True
' counting number of rows with something in A
' Column A must still be filled in.
lrow = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
' if more than one row, we must do something after closing
' the dataform
If lrow <> 1 Then
pos = 2
For counter = 2 To lrow
content = Range("A" & pos).Value
Range("A" & pos).Value = UCase(content)
pos = pos + 1
Next counter
End If
End Sub
Charlize

[Bob Phillips]

Haven't looked at this in detail, but could you use Ontime to force a change afterwards to trigger the event?

[rrtts]

@ Charlize - looks like it might work for my situation...I'll have to try and integrate it in.

@ All - Actually...I use a button on a UserForm to call up the Data Form...could I code a routine into the UserForm to force Uppercase...

For example, when the UserForm is closed...it forces the uppercase? I haven't tried it yet but I don't see why it wouldn't work.

[mdmackillop]

Having checked the Help, macro code stops when the DataForm is visible and resumes when it closes, so something as simple as the following should suffice.

Sub Macro1()
Dim cel As Range
ActiveSheet.ShowDataForm
For Each cel In Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
cel.Formula = UCase(cel)
Next
End Sub

[rrtts]

@MD - thanks...one thing though...

I have a user form with a button that activates the data form...I put your code in there and it works...but it only Upper Cases the first cell...how can I get it to Upper Case the whole row?

[mdmackillop]

Try this (untested)

Sub Macro1()
Dim cel As Range, cll as Range
ActiveSheet.ShowDataForm
For Each cel In Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
For Each cll In Intersect(cel.EntireRow, ActiveSheet.UsedRange)
cll.Formula = UCase(cel)
Next
Next
End Sub

[rrtts]

*ack*

it copied the contents of the first cell to every cell in the row...though they were all CAP...heh heh...

I guess I'll keep tinkering with it...

[mdmackillop]

Should be

cll.Formula = UCase(cll)

:rotlaugh:

[rrtts]

Well...that worked...but it knocked out the formulas I had in certain cells...

So I came up with this...


Sub UpperCase()
Dim cel As Range
For Each cel In Intersect(Cells.EntireRow, ActiveSheet.UsedRange)
If cel.HasFormula = False And Not IsEmpty(cel) Then
cel.Value = UCase(cel.Value)
End If
Next
End Sub



Seems to work...though on some sheets with lots of data it takes it a few seconds to chug thru it all...Is there anyway to speed this up?

Next question...is there any way to force a regular text box to Upper Case...I know the text boxes in VBA you can add code to do it...but I don't see how to do it with a regular text box.

Thanks for the help.

[mdmackillop]

The problem is that this sort of code is checking a lot of cells which are already caps. You should be able to capitalise text as it is added by code, or run it once for imported data. Basically, this is not an efficient way of doing things.