[Solved] creating user form and get data store in another sheet
- Thread starter Gredang
- Start date
Hello Gredang
It is great to see you again. Previously, you asked for an Entry Form with buttons like Generate, Next, Previous, Delete and Print. You have said that Office SharePoint does not allow you to use a macro. Thus, you want similar features described previously but not to apply any VBA macro.
Sorry to say that without VBA, you can not reach your goal.
However, you can use Excel's built-in Form tool. That only fulfils some of your requirements.
Steps:
Expand Customize Quick Access Toolbar >> click on More Commands.
You can visit some articles on ExcelDemy regarding your requirements.
- How to Create an Autofill Form in Excel (Step by Step Guide)
- How to Create Data Entry Form in Excel (Step by Step)
- How to Create an Excel Data Entry Form without a UserForm
- How to Create Data Entry Form with Drop Down List in Excel (2 Methods)
- How to Create a Database with Form in Excel
Regards
Lutfor Rahman Shimanto
Hi!
Ya I’ve tried but it seems like each time to enter and save the data, user needs to create the form again each time.
My sharepoint system does not allow me to open the form
So I assume there’s no other ways?
Thank you.
Dear GredangHi!
Ya I’ve tried but it seems like each time to enter and save the data, user needs to create the form again each time.
My sharepoint system does not allow me to open the form
So I assume there’s no other ways?
Thank you.
As SharePoint itself is a web-based collaboration platform, it does not allow the use of VBA macros within its interface. I am sorry for your frustration. You can create a Form using PowerApp. Later, enter the data into SharePoint from PowerApp. Connect an External Excel file with SharePoint List. Ensure that the Excel file uses a macro to fulfil your requirements. Add data again to SharePoint List. I have not tried the idea because I do not have access to SharePoint and Power App. You may try the mentioned idea. Good luck!
Excel VBA Code (Send Data From Excel to SharePoint):
Code:
Sub SendRangeToSharePointList()
Const SharePointURL As String = "https://your-sharepoint-site-url"
Const ListName As String = "Gredang SharePoint List"
Dim rng As Range
Set rng = ThisWorkbook.Worksheets("Sheet1").Range("A1:D10")
Dim xmlHttp As Object
Dim strBatchXML As String
Dim i As Long
Dim xmlBatchItem As String
Dim url As String
On Error Resume Next
Set xmlHttp = CreateObject("MSXML2.XMLHTTP.6.0")
If xmlHttp Is Nothing Then
MsgBox "Microsoft XML, v6.0 is required for this code to run. Go to Tools > References and check the box for 'Microsoft XML, v6.0'.", vbExclamation
Exit Sub
End If
On Error GoTo ErrorHandler
strBatchXML = "<?xml version=""1.0"" encoding=""UTF-8""?><Batch>"
For i = 2 To rng.Rows.Count
xmlBatchItem = "<Method ID=""" & i & """ Cmd=""New"">"
xmlBatchItem = xmlBatchItem & "<Field Name='Title'>" & rng.Cells(i, 1).Value & "</Field>"
xmlBatchItem = xmlBatchItem & "<Field Name='Column1'>" & rng.Cells(i, 2).Value & "</Field>"
xmlBatchItem = xmlBatchItem & "<Field Name='Column2'>" & rng.Cells(i, 3).Value & "</Field>"
xmlBatchItem = xmlBatchItem & "</Method>"
strBatchXML = strBatchXML & xmlBatchItem
Next i
strBatchXML = strBatchXML & "</Batch>"
url = SharePointURL & "/_vti_bin/listdata.svc/" & ListName
xmlHttp.Open "POST", url, False
xmlHttp.setRequestHeader "Content-Type", "application/xml;charset=utf-8"
xmlHttp.setRequestHeader "X-HTTP-Method", "POST"
xmlHttp.setRequestHeader "Accept", "application/atom+xml"
xmlHttp.setRequestHeader "X-RequestDigest", GetRequestDigest(SharePointURL)
xmlHttp.send strBatchXML
If xmlHttp.Status = 201 Then
MsgBox "Data successfully sent to SharePoint list.", vbInformation
Else
MsgBox "An error occurred while sending data to SharePoint list. Status: " & xmlHttp.Status & " - " & xmlHttp.statusText, vbExclamation
End If
Exit Sub
ErrorHandler:
MsgBox "An error occurred. Error: " & Err.Description, vbExclamation
End Sub
Function GetRequestDigest(ByVal url As String) As String
Dim xmlHttp As Object
Set xmlHttp = CreateObject("MSXML2.XMLHTTP.6.0")
xmlHttp.Open "POST", url & "/_api/contextinfo", False
xmlHttp.setRequestHeader "Accept", "application/json;odata=verbose"
xmlHttp.send ""
Dim jsonResponse As Object
Set jsonResponse = JsonConverter.ParseJson(xmlHttp.responseText)
GetRequestDigest = jsonResponse("d")("GetContextWebInformation")("FormDigestValue")
End FunctionRegards
Lutfor Rahman Shimanto