Point Me In The Right Direction

[josephezell]

I'm not looking for anyone to do the work for me; I want to be pointed in the right direction.

I want to create an Excel spread sheet for logging call information when our main tracking system is down. It doesn't need to be sophisticated.

I want to create a work sheet with input fields:

First Name
Last Name
ID Number
Address
Phone Number
Comments
etc
​

Once the information is entered, the user clicks a submit button and the raw data is dumped/stored on a second work sheet in the same workbook.

Outside of summing the total number of calls, there will be no calculating.

Can I do this with Excel (2007)? Do you advise using some of the Developer tools like text fields? What's the best way to get started?

Thanks for the advice.

Joe

 

[jeffreybrown]

Hi Joe,

Maybe here...

http://www.contextures.com/xlForm02.html
http://blog.contextures.com/archives/2009/04/09/worksheet-data-entry-form-in-excel/

 

[Norie]

Joe

Have you considered not using Excel?

 

[Smitty]

Welcome to the Board!

Excel's native Data Form sounds like a perfect candidate. In V.2007 you'll need to goto the Office Button-->Options-->Quick Access Toolbar and add it from the list of available controls.

For some reason it wasn't included with the Ribbon.

HTH,

 

[tusharm]

So, why not just enter the data in the 1st empty row in the "2nd worksheet in the same workbook?"

I'm not looking for anyone to do the work for me; I want to be pointed in the right direction.

I want to create an Excel spread sheet for logging call information when our main tracking system is down. It doesn't need to be sophisticated.

I want to create a work sheet with input fields:

First Name
Last Name
ID Number
Address
Phone Number
Comments
etc
​

Once the information is entered, the user clicks a submit button and the raw data is dumped/stored on a second work sheet in the same workbook.

Outside of summing the total number of calls, there will be no calculating.

Can I do this with Excel (2007)? Do you advise using some of the Developer tools like text fields? What's the best way to get started?

Thanks for the advice.

Joe

 

[josephezell]

Thanks for the input.

Maybe here...

http://www.contextures.com/xlForm02.html
http://blog.contextures.com/archives...form-in-excel/
__________________
Thanks, Jeff

Sweet. Some cool code I'll have fun playing with!

Joe

Have you considered not using Excel?

Yes.

Welcome to the Board!

Excel's native Data Form sounds like a perfect candidate. In V.2007 you'll need to goto the Office Button-->Options-->Quick Access Toolbar and add it from the list of available controls.

For some reason it wasn't included with the Ribbon.

HTH

Thanks, I'm sure it will help. I'll dig into it this weekend.

So, why not just enter the data in the 1st empty row in the "2nd worksheet in the same workbook?"

Simplicity for the user. And, yes I know what could be simpler than a column for each category - start typing. Well, they'll screw it up somehow. Heck, they are using pen and paper now!

These are people who think notepad is the supply closet full of Sticky Notes. They are still looking for the 'Any' key.

Again, thanks!

 

[josephezell]

Please take a look the following code. First of all, it doesn't work. The Range(nextRow, "A").Select causes an error.

Sub SaveData()
'
' SaveData Macro
' Saves information from Dashboard to Data
'
 
'
    Sheets("Dashboard").Select
    Range("D6").Select
    Selection.Copy
    Sheets("Data").Select
    Range(nextRow, "A").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
 
    Sheets("Dashboard").Select
    Range("H6").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Data").Select
    Range(nextRow, "B").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
 
    Sheets("Dashboard").Select
    Range("D6,H6,D8,H8,D10,H10,L10,D14,H14,D16,H16,D18,H18,D22:L23,D25:L26").Select
    Selection.ClearContents
    Range("D6").Select
    ActiveWorkbook.Save
 
End Sub

Reading it over, you may see what I am looking to do. With the execution of a macro, I want to copy something from one cell on one worksheet (Dashboard) to a cell on a second worksheet (Data) within the same workbook.

I want the macro to automatically skip to the next available cell in column "A".

In addition, I plan to incorporate "ClearContents" command into each segment before moving on to the next set of commands. In all, there are 15 cells I will copy over.

What do you recommend I do to correct and clean up this VBA code?

It seems I need to insert this code...

nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row

Thanks!

 

[Norie]

Joe

I don't have time right now to fully examine the code you've posted.

One thing I can definitely tell you is that you don't need to use Select/Selection and it can actually cause problems using it.

 

[mattm1958]

Here's my look at your code. Try executing this via "Step into" from the tools macro menu choice then after each line, "hover" over the variable names and see how they change.

Sub SaveMyData()
'
' SaveData Macro
' Saves information from Dashboard to Data
'
Dim lastrow As Long
lastrow = Worksheets("Data").Range("D65536").End(xlUp).Row ' find the last used row in column D on "Data" sheet
NextRow = lastrow + 1
Destination_Name_Col = "H"
Destination_Address_Col = "I"
Destination_Phone_Col = "J"
Source_Name_Col = "D"
Source_Address_Col = "D"
Source_Phone_Col = "D"
Source_Name_Row = 6
Source_Address_Row = 7
Source_Phone_Row = 8
'
'    Sheets("Dashboard").Select ' I think you could skip this since you are on the dashboard sheet
    InputRange = Source_Name_Col & Source_Name_Row
    NextCol = Destination_Name_Col
    Worksheets("Dashboard").Range(InputRange).Copy Destination:=Worksheets("Data").Range(NextCol & NextRow) ' cut and paste together
    Worksheets("Dashboard").Range(InputRange).ClearContents
   
    InputRange = Source_Address_Col & Source_Address_Row
    NextCol = Destination_Address_Col
    Worksheets("Dashboard").Range(InputRange).Copy Destination:=Worksheets("Data").Range(NextCol & NextRow)
    Worksheets("Dashboard").Range(InputRange).ClearContents
    
    InputRange = Source_Phone_Col & Source_Phone_Row
    NextCol = Destination_Phone_Col
    Worksheets("Dashboard").Range(InputRange).Copy Destination:=Worksheets("Data").Range(NextCol & NextRow)
    Worksheets("Dashboard").Range(InputRange).ClearContents
 
End Sub

 

[josephezell]

Joe

I don't have time right now to fully examine the code you've posted.

One thing I can definitely tell you is that you don't need to use Select/Selection and it can actually cause problems using it.

I knew there must be a better way to do this. I was just recording as I clicked. That gave me a starting point for customizing and cleaning the code. Obviously, I need to study some Visual Basic.

Thanks!

Here's my look at your code. Try executing this via "Step into" from the tools macro menu choice then after each line, "hover" over the variable names and see how they change.

Cool. I'll give it a try. Thanks!