How do I save a value from a cell from an on-line workbook to use as a variable in VBA

Question

I am expanding on an idea I had last year: How do I get the Cell value in Excel using an environmental variable for a VBA If statement

I want to use data from a license file on-line and data entered into the spreadsheet locally to verify if the license is valid and up to date

I can copy the data I need from the on-line file to the local spreadsheet without any issues and check with normal formula, but I want to avoid doing that

If possible, I'd like to save the data in variables instead to use for verification so that it makes it more difficult for someone to circumvent the protection

Sub Check_Key_Online()
Dim KeyFormula As String

On Error GoTo error

KeyFormula = "='" & "SomeWebsiteURL/[" & Reg1.Range("D9").Value & "]Lic'!$B$6"
With Reg1.Range("G9")
.Formula = KeyFormula
.Value = .Value
End With

MsgBox Reg1.Range("G9").Value = Reg1.Range("D9").Value

GoTo exit1

error:
MsgBox (IsError(KeyFormula))

exit1:
End Sub

The user will enter information, including a key, they key will then be taken to check which file to verify against on-line, once the file has been chosen and found to exist, I'd like the data to be saved in variables to check the validity

PS. Ignore the error handling code, I still have to work on how to handle things if license is not found :P

You can use a workbook Name to store the license key, or put it on a VeryHidden worksheet. In the end though, there's nothing in Excel to stop a Google-capable user from defeating your protection: the best you can do using this approach is to stop casual attempts. — Tim Williams, Oct 10 '18 at 22:18
That is the idea, lol, I want to build a system that is simple to implement, and to stop the people I work with from just copying everything, I don't want the license file to be on the local pc's, if it is on my server, I can easily switch my code off without hunting down stray copies, most of my users can only do basic things in excel, so I am not too worried about them getting past the protection — Morkai_bde, Oct 10 '18 at 22:53

How do I save a value from a cell from an on-line workbook to use as a variable in VBA

0 Answers0