Solved: Data Form Assistance

[rrtts]

On a single worksheet, I have 5 categories that contain 5 columns of "input data". In between each category is an empty column shaded gray just to provide some separation for viewing ease.

What I'm trying to do is have a data form for each category, which works if I click on the cells in a particular category and go to "Data" and select "Form".

I'm trying to automate the process where I can click a button and the data form for that category comes up. I tried recording a macro that would select the cells first and then bring up the data form for that category but it doesn't seem to work - it always brings up the data form for the first category.

Should I just create a user form for each and go from there or is there a way to make what I'm trying to do work? Or perhaps there is a more efficient way to achieve this result that I'm simply not aware of.

---
Single Worksheet - simulated
---
Cars
A1 Make
B1 Model
C1 Color

D1 (empty column)

Trucks
E1 Make
F1 Model
G1 Color

H1 (empty column)

Motorcycles
I1 Make
J1 Model
K1 Color

[lucas]

It would really help if you could post your workbook. Remove any personal info and use manage attachments after you click on post reply. I am afraid I don't understand the layout from your description and it would be easier if I could see it.

[mdmackillop]

Should Cars be A1, A2 & A3 etc.?

[rrtts]

I have attached a sample...I don't have the actual worksheet available, but it really isn't needed.

Now, within the worksheet...I can click any cell in the next avail row in the different categories and then click data + form and get a different form.

I'm just trying to automate the process and was wondering if there is a macro to do it or if I should just create all new user forms...given that there are minimal columns, I'm trying to minimize the number of sheets and/or userforms.

Hope this helps clear up what I'm trying to do.

[mdmackillop]

ShowDataForm Method Fails If Data Can't Be Found
http://support.microsoft.com/default.aspx?id=110462


Try this from any cell within the category.
[VBA]
Sub ShowDataForm()
ActiveWorkbook.Names.Add Name:="Database", _
RefersTo:="=" & ActiveSheet.Name & "!" & Cells(1, ActiveCell.Column).CurrentRegion.Address
ActiveSheet.ShowDataForm
End Sub

[/VBA]

[rrtts]

Like a champ...

You've done it again...thanks a million.

[mdmackillop]

HTH.
How about adding your country and flags to your profile. It's nice to see where other members are from.
Regards
MD

[Charlize]

Using a userform and doubleclick event. Doubleclick in A, E or I starting from row 1 to see what happens. Perhaps it will be usefull.

Charlize

[rrtts]

hey...that's pretty slick too...I'm sure I can put that to use. Thanks!

[rrtts]

Question about Data Forms...

I have a piece of code that changes all text typed into a cell to shift to upper case...however...if I call up a data form and type it in lower case then close the form...the text remains lower case.

Is there anyway to fix or work around this...?

[Charlize]

[VBA]Private Sub CommandButton1_Click()
ActiveCell.Value = UCase(Me.TextBox1.Value)
End Sub[/VBA]

[mdmackillop]

Hi Charlize,
A Dataform is not the same as a Userform, so no command button involved. Entering the data on a dataform is not triggering a Worksheet Change event, which is what I guess RTTS is using. Still thinking!

[rrtts]

@ Charlize - MD is right...but I appreciate the attempt at helping...

@ MD - I assume that means that you are able to recreate what I have described...

Here is the code I am using if it helps...it works very well except for the dataform issue...(I'm not the author of it and take no credit for creating it - but whoever did...thanks).

[vba]
Private Sub Worksheet_Change(ByVal Target As Range)
''''''''''''''''''''''''''''''''''''''''''''
'Forces all text to UPPER case
''''''''''''''''''''''''''''''''''''''''''''
'If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub
On Error Resume Next
Application.EnableEvents = False
Target = UCase(Target)
Application.EnableEvents = True
On Error GoTo 0
End Sub
[/vba]

[mdmackillop]

I guessed your code! I've posted a new question with a pointer here for some fresh input.

[Charlize]

You could try this one. Macro to show dataform and after input do the necessary stuff. ???
[vba]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:B2
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[/vba]
Charlize