在数据拉线上显示命令按钮 [英] Show a commandbutton on datapulled lines

问题描述

每3个月我们就会为我们的工程师提供一个文件.此Excel文件，从Access文件中提取数据，并以Excel格式显示.

Once every 3 months we make a file available for our engineers. This Excel files, pulls data from an Access file and shows it in Excel format.

由于某些数据没有更改，因此我们不知道工程师是否没有查看数据或值是否未更改.我要实施的是某种确认"按钮，因此我们知道显示的值实际上是已确认.

Since some of this data doesn't change, we don't know whether the engineers haven't looked at it or whether the value isn't changed. What i'm trying to implement is some sort of "confirmation" button so we know the value shown is actually confirmed.

我想做的是在我们的访问文件中输入一个称为确认"的额外列.当我们将这些数据提取到excel文件中时，我正在尝试找到一种方法来转换该确认字段"，进入命令按钮，以便每当提取数据时，每行上都会显示一个命令按钮.每当单击按钮时，数据就会保存在我们的Access文件中，因此我们知道该行已被确认.

What i'm trying to do is enter an extra column in our access file called "confirmation". When we pull this data in our excel file, i'm trying to find a way to convert that "confirmation field" into a commandbutton so whenever the data gets pulled, a commandbutton shows up on every line. Whenever the button gets clicked, the data gets saved in our Access file so we know the line is actually confirmed.

也许还有其他更简单的方法可以做到这一点?

Maybe there are some other , easier, ways to do this?

我目前有一些代码可将Excel数据保存在Access中，但不能以当前形式运行:

I currently have some code to save excel data in Access but its not working in its current form:

Sub S_SaveDataToDB()

If ActiveSheet.Name = "Estimate" Then
    ViKey = 1
Else
    ViKey = 2
End If
For i = 1 To ActiveSheet.ListObjects("TB_ACC" & ViKey).ListRows.Count
   VsData = "SET [BE] = '" & F_FilterData(ActiveSheet.Cells(7 + i, 17)) & "', [PO STATUS] = '" & F_FilterData(ActiveSheet.Cells(7 + i, 18)) & "', [REMARKS] = '" & F_FilterData(ActiveSheet.Cells(7 + i, 19)) & "', [LOGDATE] = '" & Now() & "', [LOGID] = '" & Environ("Username") & "' WHERE [PO item] = '" & ActiveSheet.Cells(7 + i, 9) & "'"
   
    If Len(F_FilterData(ActiveSheet.Cells(7 + i, 16))) + Len(F_FilterData(ActiveSheet.Cells(7 + i, 17))) + Len(F_FilterData(ActiveSheet.Cells(7 + i, 18))) > 0 Then Call S_UpdateDataInDB(VsData)
Next i

MsgBox "Data has been saved"

和

Sub S_UpdateDataInDB(VsData)
Dim cnDB As New ADODB.Connection

VsDBPath = ThisWorkbook.Sheets("Settings").Range("B2").Value
VsTable = "KCD"
cnDB.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & VsDBPath & ";" & "Jet OLEDB:Engine Type=5;" & "Persist Security Info=False;"
cnDB.Execute ("UPDATE  " & VsTable & " " & VsData)
cnDB.Close

结束字幕这里的区别是:我只想为该特定单元格保存文本(已确认数据").因此，如果要确认第8行上的数据并单击数据确认".它应仅保存数据确认"访问中的第8行.

End Sub Differences here are: I want to just save text ("Data confirmed") for that particular cell. So if one wants to confirm data on Row 8 and clicks "Data confirm". It should only save "Data confirm" for row 8 in access.

推荐答案

通常，当我尝试向某列中的每一行添加功能时，我将使用超链接.它整齐地适合单元格，可以固定到特定的单元格，并且还可以显示何时进行跟踪(颜色更改).我以一些代码为例进行了模拟.尝试使其适应您的应用程序，如果需要帮助，请告诉我.

Generally, when I'm trying to add a feature to every row in a column, I'll use a hyperlink. It fits neatly into the cell, it can be anchored to a specific cell, and it also shows when it's been followed (the color changes). I've mocked together some code as an example; try to adapt it to your application and let me know if you need help.

首先，在标准模块中，输入以下代码以创建超链接.大概是将其嵌入到提取数据的代码中.

First, in a standard module, enter the following code to create the hyperlinks. Presumably, you'd embed this into the code that pulls the data.

Sub PullData()
    Dim sh As Worksheet
    Dim lastRow As Long
    
    'Pull the data
    ' DO STUFF
    
    'Identify the range of the pulled data
    Set sh = Sheets("PulledData")
    lastRow = sh.Range("A" & sh.Rows.Count).End(xlUp).Row
    
    'Loop from row 2 through last row
    For i = 2 To lastRow
        'Assuming the 'save' option is in Column c
        sh.Cells(i, "C").Hyperlinks.Add Anchor:=sh.Cells(i, "C"), Address:="", _
        SubAddress:="", TextToDisplay:="Click To Save"
    Next i
End Sub

接下来，在包含数据的工作表的工作表代码中，输入以下代码.这告诉应用程序单击超链接时该怎么做.我创建了一个伪函数来模拟保存数据.您可以根据需要进行更改，或者，如果更适合您的需要，可以使用其他设计.

Next, in the worksheet code for the sheet with the data, enter the below code. This tells the application what to do when a hyperlink is clicked. I created a fake function that is meant to mimic saving the data. You can change this as needed, or use a different design if it suits your needs better.

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    'Confirm that this is a hyperlink in column 3

    If Not Intersect(Target.Range, Columns(3)) Is Nothing Then
        MsgBox SaveData(Target.Range)
    End If
    
End Sub

Private Function SaveData(rng As Range) As Boolean
    Debug.Print rng.Address & " has been saved."
    SaveData = True
End Function

这篇关于在数据拉线上显示命令按钮的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！