PDA

View Full Version : VBA code for Data Entry



vicky007
09-25-2016, 05:46 AM
I've a dashboard, there is button that selects one company out of 700 hundred co's and provide Data relevant to that company e.g it's status ( like "Topper"), it's revenue, it's net profit from different sheets. (1000 of rows)
Now I wana put an updated status in some "cell A10" in dashboard for a company which I just selected. Then it should "copy paste" that "updated status" to Original rows. E.g: replace the previous one.
Assume 2 sheets, one "Dashboard" and 2nd is "Data sheet"
Data sheet: 1st column contains "Co' name" and their codes in 2nd column. I usually trace via "Codes" in dashboard. Every co' contains separate code.
Their Status is in 3rd column (header name "status"). I don't wana go down to every row and change their status one by one. There are 5k to 6k rows.
Wana do that from dashboard.
Need VBA code for that.

SamT
09-25-2016, 07:45 AM
Hey, Vicky,

Welcome to VBAeXpress.

As I understand, You want to be able to put all the updates into just one place, then have VBA automatically move it to the right company's Row.

That sounds like a job for a UserForm.

Need a sample workbook with no more than 20 rows in each sheet so make sure the same 20 Co IDs are in each sheet.



Vicky, Since "CLM Range" has no meaning in Excel, I changed the Title of the Thread to better reflect our needs.

vicky007
09-25-2016, 07:56 AM
Need a sample workbook with no more than 20 rows in each sheet so make sure the same 20 Co IDs are in each sheet.

Yes sure that will work, can u provide me the link to download?

Paul_Hossler
09-25-2016, 09:52 AM
Yes sure that will work, can u provide me the link to download?

I think the idea is that you provide the sample workbook with some data

Click [Go Advanced] at the bottom right, and use the paperclip icon to select and upload the file

vicky007
09-25-2016, 10:31 AM
i've the sample file and thanx for the timely reply.

SamT
09-25-2016, 11:53 AM
Your sample was an xlsx file, so save your workbook as an xlsm file or an xlsb file, then right click on the DashBoard Tab and select View Code.

Then paste this code into that Code page
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$B$10" Then Exit Sub

Dim Found As Range

Set Found = Sheets("Data Sheet").Range("C:C").Find(Range("B2").Value)
If Not Found Is Nothing Then
Found.Offset(, 3) = Range("B10")
Else
MsgBox " Did not find " & Range("B5").Value & " in the list of companies."
End If

End Sub



If I get time today, I will develop a UserForm that you might like.

vicky007
09-25-2016, 01:35 PM
Your sample was an xlsx file, so save your workbook as an xlsm file or an xlsb file, then right click on the DashBoard Tab and select View Code.

Then paste this code into that Code page
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$B$10" Then Exit Sub

Dim Found As Range

Set Found = Sheets("Data Sheet").Range("C:C").Find(Range("B2").Value)
If Not Found Is Nothing Then
Found.Offset(, 3) = Range("B10")
Else
MsgBox " Did not find " & Range("B5").Value & " in the list of companies."
End If

End Sub



If I get time today, I will develop a UserForm that you might like.

First Thankyou for your time & effort,
I followed ur step, but this is not working. when I put value in B10, it doesnt update the status.

I cant see a Macro here, after putting ur code, even I go to create new macro then copy paste ur code, nothing saves in Module.
Since I have to assign this macro to some button afterword. So I need a something to assign.

I've attached the latest file to make it easy for you - just have a look, SAmple 2.xlb your code is already save in this sheet.

Thanks again for your time.

Paul_Hossler
09-25-2016, 02:25 PM
Made a few formula changes also




Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim iMatch As Long

'company
If Target.Address = "$B$2" Then
Application.EnableEvents = False
Range("$B$10") = Application.WorksheetFunction.VLookup(Range("$B$2"), Worksheets("Data Sheet").Range("$C:$F"), 4, False)
Application.EnableEvents = True

ElseIf Target.Address = "$B$10" Then
iMatch = Application.WorksheetFunction.Match(Range("$B$2"), Worksheets("Data Sheet").Range("$C:$C"), 0)
Worksheets("Data Sheet").Cells(iMatch, 6).Value = Range("$B$10")
End If

End Sub



I didn't understand a few comments, but look at this and see if it moves you closer

SamT
09-25-2016, 02:31 PM
Since I have to assign this macro to some button afterword. So I need a something to assign.

No. Just Change the value in B10. That will run the Macro. Technically, the Macro will run when you Exit B10 with a Tab key, Enter key, or by Selecting another cell.

See? The name of the "Macro" is Worksheet_Change


I cant see a Macro here, after putting ur code, even I go to create new macro then copy paste ur code, nothing saves in Module.
Only Macros in Standard Modules are available to the Macros menu or to Forms buttons (Controls.). Control Toolbox Controls are different. Forms control are carryovers from Excel 4

SamT
09-25-2016, 03:10 PM
OK, here's some "button" code. Put in a Standard Module.

Option Explicit

Sub Update()

Dim Found As Range
Dim Co As String
Dim Status As String

With Sheets("DashBoard")
Co = .Range("B2").Value
Status = .Range("B10")
End With

Set Found = Sheets("Data Sheet").Range("C:C").Find(Co)
If Not Found Is Nothing Then
Found.Offset(, 3) = Status
Else
MsgBox " Did not find " & Co & " in the list of companies."
End If

End Sub



BTW, That Black Shape that reads "Assign Status" is not a 'button.'

If you want a 'button' you can assign a 'macro' to, then use a Forms button. If you want a Command Button that has it's own code, then use a Control Toolbox 'button.'

SamT
09-25-2016, 03:43 PM
The attached contains a Forms button that works
And
a Control Toolbox CommandButton that works
And
Worksheet_Change code that works (after uncommenting the indicated line.)

Note: Your Black Autoshape "button" won't work.

You will need to view the Dashboard Code Page and the Module1 Code Page to see all the code.

vicky007
09-26-2016, 03:51 AM
I noticed my lookup function wasn't working fine, So I used the index function with exact match.

Yes now I can see Macro which can be assigned. Great , Thankyou for that. :clap::clap::clap:

I also cleaned up the whole sheet and attached the updated File. So someone could get some idea. Everything is working fine.

General Question. In ur last sample there are now 2 boxes, both doing the same job. working Fine.
Just curious ,About The Black Box " Command Control" I'm unable to resize this box or dont see any option to assign anything for this box.

Where do we use it usually ? Why we have to make this?:think:

Paul_Hossler
09-26-2016, 06:10 AM
I didn't see the need for the black button since it just seemed to update the Data Sheet, so in the file in post #8 I made the macro update Data Sheet

You could probably eliminate the need for it if you combine the code into the main macro

SamT
09-26-2016, 07:54 AM
About The Black Box " Command Control"
That would be the Control ToolBox CommandButton. The grey button you Assigned a macro to is a Forms CommandButton.

What it is has already been discussed.

Click the Design Mode Icon on the Visual Basic ToolBar, then you can delete it.

Paul, I leave Vicky to you, Maybe she will listen to a younger man.