• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 225
  • Last Modified:

How to modify a popup form in Excel 2003

MartinLiss was really helpful to give me a code to get a form to pop up. After i closed the question, i realized one more thing. Here's the code:

Sub ShowDataForm()
    Dim lngLastRow As Long
       
    lngLastRow = Range("A65536").End(xlUp).Row
   
    Range("A2:E" & lngLastRow).Select
    ActiveSheet.ShowDataForm
End Sub

On the columns A-E, I acutally do't want column C to show up on the form, how would i change the code to show columns A, B, D and E?
0
brasiman
Asked:
brasiman
  • 11
  • 9
2 Solutions
 
MichaelBusiness AnalystCommented:
Hi brasiman,

there are probably more elegant ways, but what you could do is this: hide the column before the dataform shows up. And unhide when you close the dataform.
Your macro code would then look like this:
Sub ShowDataForm()
    Dim lngLastRow As Long
       
    lngLastRow = Range("A65536").End(xlUp).Row
    
    Columns("C:C").EntireColumn.Hidden = True
    Range("A2:E" & lngLastRow).Select
    ActiveSheet.ShowDataForm
    Columns("C:C").EntireColumn.Hidden = False
End Sub

Open in new window

0
 
brasimanAuthor Commented:
Hi JazzyJoop. Thanks for the quick reply. Column C still shows when i do that code. :(
0
 
Martin LissRetired ProgrammerCommented:
    Sub ShowDataForm()
        Dim lngLastRow As Long
        Dim r As Range
        
        
        lngLastRow = Range("A65536").End(xlUp).Row
        
        Set r = Range("A2:A" & lngLastRow)
        Set r = Application.Union(r, Range("B2:B" & lngLastRow))
        Set r = Application.Union(r, Range("D2:D" & lngLastRow))
        Set r = Application.Union(r, Range("E2:E" & lngLastRow))
        
        ActiveSheet.ShowDataForm
    End Sub

Open in new window

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Martin LissRetired ProgrammerCommented:
Scratch that it seems that it's not possible with a data form.
.
0
 
Martin LissRetired ProgrammerCommented:
I've got an idea. Be back soon:)
0
 
Martin LissRetired ProgrammerCommented:
Yes!

    Sub ShowDataForm()
    Dim lngLastRow As Long
    Dim r As Range
    
    
    lngLastRow = Range("A65536").End(xlUp).Row
        
    ' Hide the actions going on
    Application.ScreenUpdating = False
    
    ' Temporarily move column C to column K
    Columns("C:C").Select
    Selection.Cut
    Columns("K:K").Select
    ActiveSheet.Paste
    
    ' Delete col C
    Columns("C:C").Select
    Selection.Delete Shift:=xlToLeft
    
    ' Select the report data and show the report
    Range("A2:D" & lngLastRow).Select
    ActiveSheet.ShowDataForm
    
    ' Put the saved col C data back where it belongs
    Columns("C:C").Select
    Selection.Insert Shift:=xlToRight
    Columns("K:K").Select
    Selection.Cut
    Columns("C:C").Select
    ActiveSheet.Paste
    
    Application.ScreenUpdating = True

    End Sub

Open in new window

0
 
Martin LissRetired ProgrammerCommented:
Note that I arbitrarily chose column K to store column C data, so if you have anything in that column then change all the Ks to some other letter.
0
 
brasimanAuthor Commented:
I get an error when i run it. When I click Debug, it takes me to Visual Basic and has ActiveSheet.Paste highlighted in yellow. Its the one under:

' Temporarily move column C to column K
    Columns("C:C").Select
    Selection.Cut
    Columns("K:K").Select
    ActiveSheet.Paste

Also, I just found out I need to hide columns C and D, so on the entry form only A, B and E show up.
0
 
brasimanAuthor Commented:
Ok, I guess she needs a few things. Here they are:
1. Still have columns A-E. Columns C and D she doesn't want on the form.
2. She does have data in columns F-P, that she doesn't want on the form.

So, with that said, how would we modify the code? I do get a runtime error saying ShowDataForm method of Worksheet class failed. When i click debug, it highlights ActiveSheet.Paste. I did try changing the K to Q, but i still get the mentioned error.
0
 
Martin LissRetired ProgrammerCommented:
It worked for me and so does this where I made your requested modification and also changed the name of the macro from ShowDataForm to DoReport to avoid confusion between the macro name and the native Excel function called ShowDataForm.
DataEntry.xls
0
 
brasimanAuthor Commented:
Thats weird. Ok, i'll try it again.
0
 
brasimanAuthor Commented:
So it looks like that will work. But when i insert cells at the top to give me more room, it errors out. So i changed the code to A6 instead of A2, but still get the error.
0
 
Martin LissRetired ProgrammerCommented:
Please post the workbook as you have it now.
0
 
brasimanAuthor Commented:
Here it is.
DataEntry--3-.xls
0
 
Martin LissRetired ProgrammerCommented:
I'm getting an error that I don't understand yet but I have to leave for a few hours. I'll get back to you.
0
 
brasimanAuthor Commented:
Ok, thank you! I appreciate the heads up on timing so i don't just wait. :) Thanks!
0
 
Martin LissRetired ProgrammerCommented:
This works for me.

    Sub DoReport()
    Dim lngLastRow As Long
    Dim r As Range
    Dim lngFirstRow As Long
    Dim lngRow As Long
    
    lngLastRow = Range("A65536").End(xlUp).Row
    For lngRow = 1 To 1000
        If Cells(lngRow, 1) <> "" Then
            lngFirstRow = lngRow
            Exit For
        End If
    Next
        
    ' Hide the actions going on
    Application.ScreenUpdating = False
    
    With ActiveSheet
        ' Temporarily move column C & D to column K & L
        .Columns("C:D").Select
        Selection.Cut
        .Columns("K:L").Select
        .Paste
        
        ' Delete col C & D
        Columns("C:C").Select
        Selection.Delete Shift:=xlToLeft
        ' This isn't a mistake. Old column D is now C
        .Columns("C:C").Select
        Selection.Delete Shift:=xlToLeft
        
        ' Select the report data and show the report
        Range("A4:C" & lngLastRow).Select
        
        ' Select the data for the report
        .Range("A" & lngFirstRow & ":C" & lngLastRow).Select
        Range(Selection, Selection.End(xlDown)).Select
        ' For some resaon this is required
        Selection.Name = "Database"
        
        .ShowDataForm
        
        ' Put the saved col C data back where it belongs
        .Columns("B:B").Select
        Selection.Insert Shift:=xlToRight
        .Columns("B:B").Select
        Selection.Insert Shift:=xlToRight
        .Columns("K:L").Select
        Selection.Cut
        .Columns("B:C").Select
        .Paste
    End With
    Application.ScreenUpdating = True

    End Sub

Open in new window

0
 
Martin LissRetired ProgrammerCommented:
Did it work for you?
0
 
brasimanAuthor Commented:
That worked. Thank you for your help!!
0
 
brasimanAuthor Commented:
You're awesome MartinLiss. Thanks for your help and patience with me. Its working great.
0
 
Martin LissRetired ProgrammerCommented:
You're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2013
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 11
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now